Собрать данные с таблиц с изменяющимися столбцами в PowerQuery

Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?

---

Данная статья рассчитана на тех пользователей, которые уже умеют получать данные из файлов и таблиц при помощи PowerQuery. Подробная инструкция по получению данных здесь не приводится. Если получение данных для вас пока еще тоже темный лес, то лучше начать с просмотра статьи, в которой я подробно показывал и рассказывал как можно объединять информацию из нескольких файлов в папке в одну таблицу при помощи PowerQuery(План-фактный анализ в Excel при помощи Power Query).
Если вкратце, то помещаем нужные файлы в папку, далее идем на вкладку Данные(Data) или Power Query -Получить данные(Get Data) - Из файла(From file) -Из папки(From folder). Указываем папку, в которой расположены файлы для получения данных, выбираем объекты. Далее все по обычному сценарию:

• устанавливаем фильтры на нужные файлы и таблицы
• получаем содержимое таблиц через создание пользовательского столбца: Добавить столбец(Add column) -Пользовательский столбец(Custom column). Задаем имя создаваемого столбца, а для получения данных книг Excel используем формулу:
  =Excel.Workbook([Content])
• после этого мы получаем несколько стандартных столбцов, среди которых есть столбец Data, который как раз содержит данные таблиц и листов, которые нам и надо получить

небольшое видео процесса:

Добрались до сути дела. Для чего обычно делают подключение к папке? Чтобы иметь возможность получить данные из всех таблиц всех файлов и обновлять эти данные из самого запроса. Все отлично сработает и не потребует дополнительных танцев с бубном, если файлы в папке всегда имеют одинаковую структуру и наименования столбцов. Но как быть если в папку складываются файлы от региональных менеджеров, где в качестве заголовков столбцов помимо Даты и Суммы могут быть заранее неизвестные названия столбцов? Название региона, отдела, ФИО менеджеров? Возьмем такие две таблицы из двух разных файлов:

Вроде бы ничего не намекает на проблему. После описанных выше действий просто разворачиваем данные столбца Data:

Но здесь и есть главный нюанс: PowerQuery все действия записывает пошагово в виде запроса. Полный текст запроса отображается в Расширенном редакторе. И когда мы разворачиваем столбец Data, в запросе "жестко" текстом прописываются имена всех столбцов таблиц, которые попадут в запрос. Вот так это будет выглядеть для PowerQuery:
= Table.ExpandTableColumn(#"Удаленные столбцы1", "Data", {"Дата", "Общая выручка", "Выручка по Иванову", "Выручка по Петрову", "Выручка по Сергееву"}, {"Дата", "Общая выручка", "Выручка по Иванову", "Выручка по Петрову", "Выручка по Сергееву"})
А это означает, что если в папку будет помещен файл, в котором есть столбцы, не перечисленные в запросе - они просто не попадут в обработку. И наоборот - если в запросе будут имена столбцов, которых нет ни в одном файле - получим либо ошибку запроса либо null для всех данных столбца. И вот это уже становится большой проблемой на пути к автоматизации.
Чтобы избежать подобных ошибок нам необходимо каким-то образом заранее узнать имена всех столбцов всех полученных таблиц и именно их подсунуть в запрос.
Прежде чем читать далее и пробовать применить, необходимо знать как вызвать расширенный редактор: переходим в нужный запрос -Главная -Расширенный редактор:

Еще лучше - если опыт работы с расширенным редактором уже есть.
На примере исходного запроса такого вида(скопировано из Расширенного редактора):
let
Источник = Folder.Files("G:\Работа с Excel\1_PowerQuery\PowerQuery таблицы с разными столбцами"),
#"Строки с примененным фильтром" = Table.SelectRows(Источник, each ([Extension] = ".xlsx")),
#"Удаленные столбцы" = Table.RemoveColumns(#"Строки с примененным фильтром",{"Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Удаленные столбцы", "Данные", each Excel.Workbook([Content])),
#"Развернутый элемент Данные" = Table.ExpandTableColumn(#"Добавлен пользовательский объект", "Данные", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
#"Строки с примененным фильтром1" = Table.SelectRows(#"Развернутый элемент Данные", each ([Kind] = "Table")),
#"Удаленные столбцы1" = Table.RemoveColumns(#"Строки с примененным фильтром1",{"Name.1", "Item", "Kind", "Hidden"}),
#"Развернутый элемент Data" = Table.ExpandTableColumn(#"Удаленные столбцы1", "Data", {"Дата", "Общая выручка", "Выручка по Иванову", "Выручка по Петрову", "Выручка по Сергееву"}, {"Дата", "Общая выручка", "Выручка по Иванову", "Выручка по Петрову", "Выручка по Сергееву"})
in
#"Развернутый элемент Data"
этот запрос делает все, что я описал выше, но с фиксированными столбцами. Красным я выделил те данные, которые нам необходимо изменять динамически в зависимости от имен столбцов. А сделаем мы это при помощи вот таких строк на языке M:

ColNames = Table.AddColumn(#"Удаленные столбцы1", "cols", each Table.ColumnNames([Data]), type list),
allCols = List.Sort(List.Distinct(List.Combine(ColNames[cols]))),
#"Развернутый элемент Data" = Table.ExpandTableColumn(#"Удаленные столбцы1", "Data", allCols, allCols)
in
#"Развернутый элемент Data"

• ColNames = Table.AddColumn(#"Удаленные столбцы1", "cols", each Table.ColumnNames([Data]), type list) - этой строкой мы создаем новый пользовательский столбец, в котором перечислены имена столбцов всех таблиц(each Table.ColumnNames([Data]))
• allCols = List.Distinct(List.Combine(ColNames[cols])) - здесь мы объединяем все столбцы в один список(List.Combine(ColNames[cols])) и удаляем из списка дубликаты(List.Distinct). Кстати, один из известных спецов по Power Query Максим Зеленский предложил более изящный вариант этой строки: allCols = List.Union(ColNames[cols]). Получается даже чем-то лучше, т.к. столбцы располагаются в более правильном порядке.
• #"Развернутый элемент Data" = Table.ExpandTableColumn(#"Удаленные столбцы1", "Data", allCols, allCols) - это по сути та же строка, что в исходном запросе, но имена столбцов сюда уже подставляются только те, которые есть в таблицах для выгрузки.

Полностью запрос теперь выглядит так:
let
Источник = Folder.Files("G:\Работа с Excel\1_PowerQuery\PowerQuery таблицы с разными столбцами"),
#"Строки с примененным фильтром" = Table.SelectRows(Источник, each ([Extension] = ".xlsx")),
#"Удаленные столбцы" = Table.RemoveColumns(#"Строки с примененным фильтром",{"Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Удаленные столбцы", "Данные", each Excel.Workbook([Content])),
#"Развернутый элемент Данные" = Table.ExpandTableColumn(#"Добавлен пользовательский объект", "Данные", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
#"Строки с примененным фильтром1" = Table.SelectRows(#"Развернутый элемент Данные", each ([Kind] = "Table")),
#"Удаленные столбцы1" = Table.RemoveColumns(#"Строки с примененным фильтром1",{"Name.1", "Item", "Kind", "Hidden"}),
ColNames = Table.AddColumn(#"Удаленные столбцы1", "cols", each Table.ColumnNames([Data]), type list),
allCols = List.Sort(List.Distinct(List.Combine(ColNames[cols]))),
#"Развернутый элемент Data" = Table.ExpandTableColumn(#"Удаленные столбцы1", "Data", allCols, allCols)
in
#"Развернутый элемент Data"
Теперь какие бы таблицы мы не кидали в папку - запрос получит из них данные без дополнительных действий с нашей стороны.
Но не стоит забывать: если дальше в запросе так же идет обращение к столбцам, которые могут изменяться - это тоже придется учесть в запросе и так же составлять некие "динамические" заголовки.

Так же см.:
Power Query - что такое и почему её необходимо использовать в работе?
Относительный путь к данным PowerQuery