Как правильно разворачивать вложенные таблицы в Power Query

Предположим, что у нас есть файл Excel с несколькими "умными" таблицами:

Исходные таблицы

Если стандартным образом загрузить эти таблицы в Power Query с помощью команды Данные - Получить данные - Из файла - Из книги (Data - Get Data - From file - From Workbook), то мы получим примерно следующее:

Загруженные таблицы в Power Query

Картинка, думаю, знакомая многим пользователям Power Query. Подобные вложенные таблицы можно увидеть после объединения запросов (а-ля ВПР), группировки (команда Группировать по на вкладке Преобразование), импорте всех файлов из заданной папки и т.д.

Следующим логичным шагом в подобной ситуации обычно бывает разворачивание всех вложенных таблиц разом - с помощью кнопки с двойными стрелками в шапке столбца Data:

Разворачивание вложенных таблиц

В результате получим сборку всех строк из всех таблиц в единое целое. Всё хорошо, просто и понятно. 

Теперь представим, что в исходных таблицах добавили новый столбец (Скидка) и/или удалили один из имеющихся (Город):

Измененные данные

Тогда наш запрос после обновления вернет уже не такую красивую картинку - скидка так и не появилась, а столбец город стал пустым, но не пропал:

Имена столбцов в формуле

И легко сообразить почему - в строке формул хорошо видно, что имена развёрнутых столбцов жёстко прописаны в аргументах функции Table.ExpandTableColumn в виде списков в фигурных скобках.

Обойти эту проблему несложно. Во-первых, давайте получим имена столбцов из шапки любой (например, первой) таблицы с помощью функции Table.ColumnNames. Это будет выглядеть как:

Получаем список названий столбцов

Здесь:

  • #"Другие удаленные столбцы" - имя предыдущего шага, откуда мы берём данные
  • {0} - номер таблицы, из которой мы извлекаем шапку (считая с нуля, т.е. 0 - это первая таблица)
  • [Data] - имя столбца на предыдущем шаге, где лежат разворачиваемые таблицы

Осталось полученную в строке формул конструкцию подставить их в функцию Table.ExpandTableColumn на шаге разворачивания таблиц вместо жёстко прописанных списков. Выглядеть всё это в итоге должно так:

Готовое решение

Вот и всё. И никаких проблем с разворачиванием вложенных таблиц при изменении исходных данных больше не будет.

Ссылки по теме

 


20.03.2022 21:08:33
так и не приспособился к этой поуэр куэри
06.04.2022 13:11:19
Зря, мощная тема! Из 1С выгружаю таблицы, свожу с помощью PQ...никаких макросов, впр...
12.05.2022 10:29:16
Вау, ну просто вау! Снимаю шляпу и всё такое.  Мега полезная вещь!
27.07.2022 12:28:05
Благодарю за великолепный прием .
11.09.2022 18:20:56
Доброго дня!!!  А как быть с неправильно установленным типом данных?? вернее сказать как привязать столбцы и их типы данных ?? потому как к примеру дату он выдает как числа и если изменять тип данных при последущем изменении кол-ва стобцов
PQ выдает ошибку(возможно только у меня)
Всем доброго дня!!!
Может кто знает как применить сортировку данных в столбце например номер 7 но каждый раз с новым названием?
Заранее благодарю!
04.09.2023 16:34:14
сошлись на Table.ColumnList(Source){6}
15.12.2023 11:37:46
А что если объединяемые таблицы имеют разное количество столбцов? Судя по примеру, берется список столбцов из первого по списку файла, а во втором например может быть столбец которого нет в первом по строке. И его не "зацепит" приём.
Предлагаю такой вариант: =Table.ColumnNames(Table.FirstN(Table.Combine(Table[Data]),0)) //Объединяем через combine все файлы котор нужно собрать, колонка называется [Data] после обработки функцией. Затем оборачиваем всё в сохранить первые строки = 0, нас интересуют названия столбцов из всех файлов. И третье - Table.ColumnNames, получаем список всех столбцов из всех файлов. Шаг обзываем "List' Далее добавляем шаг после = Table.ExpandTableColumn(Table, "Data", List, List)
Наверх