Допустим, у нас есть файл Excel с несколькими смарт-таблицами:
Если загрузить эти таблицы в Power Query стандартным способом с помощью команды Данные – Получить данные – Из файла – Из книги (Данные — Получить данные — Из файла — Из книги), то получим что-то вроде этого:
Картина, думаю, знакома многим пользователям Power Query. Подобные вложенные таблицы можно увидеть после объединения запросов (а-ля ВПР), группировки (команда Группа по таб трансформация), импорт всех файлов из заданной папки и т. д.
Следующим логическим шагом в этой ситуации обычно является развертывание всех вложенных таблиц сразу — с помощью кнопки с двойными стрелками в заголовке столбца. Данные:
В результате мы получаем сборку всех строк из всех таблиц в единое целое. Все хорошо, просто и понятно.
Теперь представьте, что в исходные таблицы добавлен новый столбец (Скидка) и/или удален один из существующих (Город):
Тогда наш запрос после обновления вернет не такую красивую картинку — скидка не появилась, а столбец города стал пустым, но не исчез:
И легко понять, почему: в строке формул хорошо видно, что имена развернутых столбцов жестко закодированы в аргументах функции. Таблица.ExpandTableColumn в виде списков в фигурных скобках.
Обойти эту проблему легко. Для начала получим имена столбцов из заголовка любой (например, первой) таблицы с помощью функции Table.ColumnNames. Это будет выглядеть так:
Вот:
- #”Другие столбцы удалены” – название предыдущего шага, откуда мы берем данные
- 0 {} — номер таблицы, из которой мы извлекаем заголовок (считаем с нуля, т.е. 0 — это первая таблица)
- [Данные] – имя столбца на предыдущем шаге, где расположены развернутые таблицы
Осталось подставить полученную в строке формул конструкцию в функцию Таблица.ExpandTableColumn на этапе расширения таблиц вместо жестко запрограммированных списков. В итоге все должно выглядеть так:
Вот и все. И проблем с расширением вложенных таблиц при изменении исходных данных больше не будет.
- Построение многоформатных таблиц из одного листа в Power Query
- Создавайте таблицы с разными заголовками из нескольких файлов Excel.
- Сбор данных со всех листов книги в одну таблицу