Страницы: 1
RSS
объединение столбцов с повторяющимися названиями в PowerQuery
 
Коллеги, кто работает с PowerQuery, подскажите, как создать плоскую таблицу в PowerQuery, если в таблице на листе Excel названия столбцов повторяются.
Главная засада: VBA не используем, названия столбцов на листе Excel не меняем.

Поясню на примере. На листе Excel есть "плохая таблица", первый столбец, например, "Клиент", а дальше идут группы столбцов с повторяющимися названиями, типа "Дата", "Количество". На листе их менять нельзя. Таких групп может быть очень много (десятки), или мало (5-6), или через месяц количество столбцов может измениться, или в другом исходном файле оно будет отличаться.

Из этой таблицы нужно получить  сведенную таблицу, в которой будут только три столбца: "Клиент", "Дата", "Количество". То есть, все данные из второй группы столбцов "Дата-Количество должны дописаться (вместе с именем клиента) в конец таблицы, затем третьей и т.п.

Соответственно, если затаскивать таблицу в PowerQuery, указывая, что первая строка содержит заголовки, хитрый PQ создает из диапазона умную таблицу и у нее получаются заголовки типа: Дата Количество Дата2 Количество3 Дата4 Количество5. Соответственно, UnpivotOtherColumns тут не помогает - в столбце будут просто перечислены разные названия столбцов.

Ок, говорим PQ, что таблица не содержит заголовки. Это чудо инженерной мысли всё равно создаст нам таблицу, добавив над ней строку с именами столбцов типа Столбец1, Столбец2, ... Столбец7 и так далее. В первой строке таблицы у нас есть настоящие заголовки. Не знаю, стало ли легче... Вроде не намного.

Как PowerQuery заставить сделать такую таблицу, не определяя заранее количество групп столбцов?

Есть ручное решение: создаем N (по количеству групп) выборок столбцов ({1,2,3},{1,4,5},{1,6,7} и так далее), и затем делаем им всем Append.
Но это решение требует постоянного количества групп.
Изменено: Максим Зеленский - 14.10.2015 22:11:41
F1 творит чудеса
 
На вашем примере после добавления таблицы как источника добавьте операцию "Отменить свертывание других столбцов":

Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Клиент", type text}, {"Дата", type datetime}, {"Количество", Int64.Type}, {"Дата2", type datetime}, {"Количество3", Int64.Type}, {"Дата4", type datetime}, {"Количество5", Int64.Type}}),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Измененный тип", {"Клиент"}, "Атрибут", "Значение")
in
    #"Другие столбцы с отмененным свертыванием"


Далее думаю уже дело техники как отсечь цифры
 
StepanWolkoff, Спасибо за ответ!

Пробовал такое.
Обрезаем до 4 символов:
Код
= Table.TransformColumns(#"Другие столбцы с отмененным свертыванием", {{"Атрибут", each Text.Start(_, 4), type text}})

и получаем вот такую таблицу:

Если сделать Pivot по столбцу "Атрибут", то получится чепуха. Мне не надо агрегировать данные, но если задать такую команду без агрегирования:
Код
= Table.Pivot(#"Извлеченные первые символы", List.Distinct(#"Извлеченные первые символы"[Атрибут]), "Атрибут", "Значение")

то получаем таблицу ошибок.

Полуплоская таблица мне не нужна в данном случае, так как в одном столбце собираются разнотипные данные (даты и количества). И агрегирование их не подходит, потому что нужны все записи.
Если смотреть на такой способ, нужно получить в этом столбце такие записи: Дата1, Коли1, Дата2, Коли2, Дата3, Коли3 и так далее. Тогда, после разделения столбца на 2 (4 символа в первом), мы получим второй столбец с номерами групп, и уже после этого можно делать Pivot по первому столбцу. Тогда данные не агрегируются, второй столбец можно после сведения удалить, красота.
Вручную так получалось.

Вот и вопрос - как заставить переименовать неопределенное количество столбцов, или, может быть, сделать доп.столбец, с нумерацией таких групп последовательно по 2 строки? Только не соображу, как заставить функцию учитывать номер строки.
F1 творит чудеса
 
Максим Зеленский, смотрите во вложении. Нужно конечно на реальных данных потестировать, как будут присваиваться индексы, но думаю, что все корректно будет работать.
 
Цитата
Максим Зеленский написал:
Только не соображу, как заставить функцию учитывать номер строки.
Собственно, вот оно, правильно заданный вопрос дает 80% ответа:
1. UnpivotOtherColumns
2. Обрезаем бывшие названия столбцов до одинаковой длины, чтобы они образовали повторяющиеся группы
3. Добавляем Индекс
4. Делаем Custom-столбец с формулой Индекс/2, округленный вверх - получаем строки 1,1,2,2,3,3,4,4 и т.д.
5. Удаляем Индекс
6. Pivot Атрибут
7. Удаляем Custom

Бинго! :)

Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(Источник, {"Клиент"}, "Атрибут", "Значение"),
    #"Извлеченные первые символы" = Table.TransformColumns(#"Другие столбцы с отмененным свертыванием", {{"Атрибут", each Text.Start(_, 4), type text}}),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Извлеченные первые символы", "Индекс", 1, 1),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Добавлен индекс", "Custom", each Number.RoundUp([Индекс]/2)),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"Индекс"}),
    #"Сведенный столбец" = Table.Pivot(#"Удаленные столбцы", List.Distinct(#"Удаленные столбцы"[Атрибут]), "Атрибут", "Значение"),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Сведенный столбец",{"Custom"}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Удаленные столбцы1",{{"Дата", type date}, {"Коли", type number}, {"Клиент", type text}})
in
    #"Измененный тип"
F1 творит чудеса
 
StepanWolkoff, спасибо! Тоже интересное решение!
F1 творит чудеса
 
Тоже хорошо )) даже лучше))
 
Максим Зеленский, Спасибо за готовое решение
у меня аналогичная проблема как из стобца с атрибутами сделать плоскую таблицу в Power Query
один только вопрос Максим, команда table.pivot - недоступна из панели команд или контекстного меню, она есть только для редактора, верно?
 
Blood81, о, как вы тему раскопали :) по ее мотивам 4 разных решения есть, плюс еще одно в комментах: http://excel-inside.pro/blog/2015/11/16/stacking-non-nested-groups-of-repeating-columns-in-power-query/
Table.Pivot вызывается кнопкой "Столбец сведения" на вкладке "Преобразование"
F1 творит чудеса
 
Максим Зеленский, ну дык поиск творит чудеса.... но как оказалось недостаточно спасибо вам за новые ссылки пошел учить )))
 
И кстати если зайти по ссылке от Максима и изучить именно 4-й способ (это где использование команды Record.FromList) можно играться и с 3 и 4 и т.д. повторяющимся группами столбцов. Например может повторяться (2) Дата - Количество, а может (3) Дата – Сумма - ДатаСерт, или (4) Дата – Сумма - ДатаСерт - СуммаРезерв и т.д.

Шаги в статье такие:

1.      Promote headers
2.      Unpivot al columns except common columns. Now our repeating columns becomes repeating groups of rows.
3.      Add “Index” column
4.      Transform “Index” column with use of Number.IntegerDivide function. This can be done manually or with UI: Select “Index” column, go to Transform – From Number – Standard – Divide (Integer). Our goal is to get whole number of “Index”/2 (as we have two repeating columns).

Если повторяется 3 названия то “Index”/3, если 4 - “Index”/4 и т.д.

5.      Now each pair of row has the same corresponding number.
6.      And here is some magic: now we’ll group rows by this modified “Index”, but as an aggregate function we’ll use non-standard Records.FromList function. It is not in list of functions available in dialog window, so it should be entered manually or by editing of other aggregate function code:

2 столбца:
Код
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Имя", "Заказ", "Index"}, {{"Значения", each Record.FromList([Value], {"Дата","Сумма"}), type record}}),

3 столбца:
Код
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Имя", "Заказ", "Index"}, {{"Значения", each Record.FromList([Value], {"Дата","Сумма","ДатаСерт"}), type record}}),

4 столбца:
Код
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Имя", "Заказ", "Index"}, {{"Значения", each Record.FromList([Value], {"Дата","Сумма","ДатаСерт","СуммаРезерв"}), type record}}),


Т.е. просто дописываются названия новых повторяющихся столбцов.

7.     Now we can remove “Index” and expand “Values” column by clicking on double arrow right to column name, and we got the table we need!
8.     Make sure all data has correct type assigned, close & load.


Прикрепил файл с примерами.
Изменено: VasiliePavlov - 02.02.2017 13:47:12
 
Спасибо за интересное дополнение!
F1 творит чудеса
 
Доброе время суток.
Вариант с динамическим определением повторяющихся названий столбцов. Так как они могут быть только на листе (в "умных" таблицах дубли запрещены"), то данные считываются с указанного листа.
Страницы: 1
Наверх