Если еще не работали с надстройкой 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:
Развернуть столбец 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

5 комментариев

  1. Дмитрий, а почему бы в шаге allCols сразу не собрать все таблицы из столбца ColNames[cols] через Table.Combine
    allCols = Table.Combine( ColNames[cols] )
    И не нужно заморачиваться с динамическим списком заголовков а также следующий шаг уже не потребуется.

    1. Алексей, не совсем понял. А как мы будем делать Table.Combine, если там в качестве параметров требуются именно таблицы, а у нас только имена столбцов в виде List? Или я может не так понял задумку - может в другом месте где надо добавить предлагаемый шаг, а не после ColNames? Или сам шаг ColNames тоже надо будет менять, чтобы создать List из самих таблиц. Можете написать свое видение - статья будет полезней. Спасибо.

      1. Дмитрий, не люблю я плодить столбцы без надобности и для себя пришел к такому решению. Сразу после шага #"Удаленные столбцы1" пишем такое:
        #"Развернутый элемент Data" = Table.ExpandTableColumn(#"Удаленные столбцы1", "Data", List.Union( List.Transform( #"Удаленные столбцы1"[Data], (x) => Table.ColumnNames(x) ) ) )
        Четвертый аргумент, он не обязательный, нужен только если надо что-то переименовать, потому его опускаю.

  2. Ага, кажется сам догадался, тогда теряется источник файла, и потребуется добавлять столбец с названием файла в каждую таблицу перед комбинированием. Но тем не менее такой способ, на мой взгляд, тоже имеет право на существование. А если данные об источнике не важны, то он проще.

  3. Здравствуйте! Спасибо за полезную статью! Подскажите, пожалуйста, как изменить значение сразу всех столбцов (например тип данных), вместо перечисления имён столбцов?

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.