Как правильно разворачивать вложенные таблицы в Power Query
Предположим, что у нас есть файл Excel с несколькими "умными" таблицами:
Если стандартным образом загрузить эти таблицы в Power Query с помощью команды Данные - Получить данные - Из файла - Из книги (Data - Get Data - From file - From Workbook), то мы получим примерно следующее:
Картинка, думаю, знакомая многим пользователям Power Query. Подобные вложенные таблицы можно увидеть после объединения запросов (а-ля ВПР), группировки (команда Группировать по на вкладке Преобразование), импорте всех файлов из заданной папки и т.д.
Следующим логичным шагом в подобной ситуации обычно бывает разворачивание всех вложенных таблиц разом - с помощью кнопки с двойными стрелками в шапке столбца Data:
В результате получим сборку всех строк из всех таблиц в единое целое. Всё хорошо, просто и понятно.
Теперь представим, что в исходных таблицах добавили новый столбец (Скидка) и/или удалили один из имеющихся (Город):
Тогда наш запрос после обновления вернет уже не такую красивую картинку - скидка так и не появилась, а столбец город стал пустым, но не пропал:
И легко сообразить почему - в строке формул хорошо видно, что имена развёрнутых столбцов жёстко прописаны в аргументах функции Table.ExpandTableColumn в виде списков в фигурных скобках.
Обойти эту проблему несложно. Во-первых, давайте получим имена столбцов из шапки любой (например, первой) таблицы с помощью функции Table.ColumnNames. Это будет выглядеть как:
Здесь:
- #"Другие удаленные столбцы" - имя предыдущего шага, откуда мы берём данные
- {0} - номер таблицы, из которой мы извлекаем шапку (считая с нуля, т.е. 0 - это первая таблица)
- [Data] - имя столбца на предыдущем шаге, где лежат разворачиваемые таблицы
Осталось полученную в строке формул конструкцию подставить их в функцию Table.ExpandTableColumn на шаге разворачивания таблиц вместо жёстко прописанных списков. Выглядеть всё это в итоге должно так:
Вот и всё. И никаких проблем с разворачиванием вложенных таблиц при изменении исходных данных больше не будет.
Ссылки по теме
- Сборка разноформатных таблиц с одного листа в Power Query
- Сборка таблиц с разными шапками из нескольких файлов Excel
- Сборка данных со всех листов книги в одну таблицу
PQ выдает ошибку(возможно только у меня)
Может кто знает как применить сортировку данных в столбце например номер 7 но каждый раз с новым названием?
Заранее благодарю!