Иногда у вас есть таблица данных, которая описывает отношение 1 ко многим. Количество значений неизвестно и может варьироваться. Поэтому написание кода Power Query для разворота может оказаться непростой задачей. Давайте рассмотрим пример.
Первый столбец – это список городов присутствия офисов. Второй столбец – это список отделов в каждом офисе.
Как вы можете видеть, не в каждом офисе есть все типы отделов, но в каждом офисе есть один или несколько отделов.
Я хочу преобразовать исходную таблицу в следующую форму:
Обычно я бы посоветовал вам оставить его в табличном формате, поскольку он идеально подходит для дальнейшего анализа с помощью таблиц Pivot или в Power BI.
Но если вы хотите создать окончательный отчет в Power Query, то эта статья для вас.
Работа в Power Query
Мои данные уже находятся в таблице, поэтому далее я загружаю таблицу в Power Query.
Для начала мне нужно сгруппировать строки по местоположению.
Это дает следующую таблицу со столбцом таблиц. Таблица в каждой строке столбца “Группировка” содержит местоположение и отделы для местоположения в этой строке.
Далее мне нужно транспонировать столбец “Департамент” в этих таблицах, превратив строки в столбцы. Для этого я могу использовать функцию Table.Transpose.
Но поскольку я хочу транспонировать только один столбец, а не всю таблицу, я воспользуюсь функцией Table.SelectColumns, чтобы взять только этот столбец для Table.Transpose.
Добавьте пользовательский столбец и введите следующий код
1
=Table.Transpose(Table.SelectColumns([Группировка],{"Департамент"}))
На данном этапе вы можете воспользоваться графическим интерфейсом, дважды щелкнув по стрелке с двойным направлением на заголовке пользовательского столбца, чтобы развернуть эти новые таблицы.
Но если я сделаю это, то будет создано только три новых столбца, поэтому щелкните на “Загрузить еще” и только потом нажмите “ОК”.
Однако проблема здесь заключается в том, что в коде M жестко закодировано количество создаваемых столбцов.
Функция Table.ExpandTableColumn использует четыре аргумента (параметра):
- Таблица, содержащая колонку для расширения
- Имя расширяемого столбца
- Имена столбцов внутри расширяемого столбца
- Имена новых столбцов, которые будут созданы в результате расширения (не обязательный аргумент, можно удалить).
В существующем виде этот код создаст только пять столбцов, поэтому если в исходных данных их больше, они не появятся в итоговой таблице.