Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

Собрать данные с таблиц с изменяющимися столбцами в 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:
Развернуть столбец 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


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: 4 комментария
  1. Алексей:

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

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

  2. Алексей:

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

  3. Владимир:

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

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти