Объединение однотипных отчетов с помощью Power Query

Если каждый месяц или каждую неделю вы складываете однотипные отчеты в одну папку, Power Query подходит для задачи лучше ручного копирования листов. Смысл подхода в том, что вы один раз настраиваете получение данных из папки, задаете фильтры для нужных файлов и проверяете логику преобразований на одном образце. После этого новые файлы можно просто добавлять в ту же папку и обновлять запрос.

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

Подготовка исходных таблиц

Перед объединением проверьте не только сами таблицы, но и устройство папки. Для коннектора Folder Power Query показывает файлы не только из выбранной папки, но и из подпапок. На практике это значит, что в объединение могут случайно попасть архивы, тестовые выгрузки, старые копии и промежуточные файлы, если они лежат ниже по структуре.

Поэтому полезно заранее развести рабочие данные и все лишнее. Например, хранить только готовые отчеты в одной папке, а черновики и архив — в другой. Если это невозможно, тогда фильтрация по Folder Path становится обязательным шагом. Второй базовый фильтр — по расширению файла, чтобы в объединение не попали документы другого типа.

Еще до подключения откройте 2–3 файла и проверьте одно и то же место с данными: одинаковы ли названия столбцов, нет ли добавленного служебного столбца, не изменился ли лист или имя таблицы внутри книги. Power Query умеет массово применять один и тот же сценарий обработки ко всем файлам, но если один файл устроен иначе, именно он чаще всего ломает обновление.

Как отфильтровать файлы и запустить объединение

Когда источник — папка, безопаснее сначала выбрать режим преобразования данных, а не мгновенное объединение. Так вы попадаете в таблицу со списком файлов и можете вручную отобрать только нужные строки. В этом представлении обычно есть столбец Content с двоичными значениями файла, а также поля вроде Extension и Folder Path, по которым удобно делать отбор.

Практический порядок такой: подключаете папку, открываете список файлов, фильтруете расширение, затем при необходимости фильтруете точный путь к рабочей папке или часть имени файла. Только после этого запускаете команду объединения по столбцу Content или через соответствующую команду на вкладке редактора. Это важнее, чем кажется: если объединять все сразу, а фильтровать потом, в сценарий уже могут попасть лишние или проблемные файлы.

После команды объединения Power Query попросит выбрать пример файла. По умолчанию это часто первый файл в списке, но не обязательно лучший образец. Лучше выбрать типичный файл без пропусков и с полной структурой. Именно по нему Power Query определяет, как открывать содержимое и какие шаги применять дальше ко всем остальным файлам.

Что Power Query создает автоматически

После объединения создается не сводная таблица Excel, а объединенный запрос с данными. Для пользователя это важное различие: вы получаете общую таблицу строк, которую можно дальше очищать, фильтровать, загружать на лист или использовать в модели данных. Уже после этого при желании можно строить сводную таблицу, но это отдельный этап.

Автоматически Power Query создает несколько связанных элементов: пример запроса для одного файла, функцию, которая применяет те же действия к каждому файлу, и итоговый запрос, где результаты по всем строкам списка файлов разворачиваются в единую таблицу. У этой схемы есть плюс: если вы исправляете шаги в запросе-образце, изменения отражаются и на обработке остальных файлов.

Но здесь же скрыта типичная ошибка. Если вы меняете названия столбцов, удаляете поля или оставляете автоматически добавленный шаг смены типов без проверки, итоговый запрос может начать выдавать ошибки уже на этапе раскрытия данных. Поэтому после каждого заметного изменения полезно не только смотреть на предпросмотр, но и пролистывать последние шаги в итоговом запросе.

Учебный пример: как проверить, что объединение настроено правильно

Учебный пример. Представим папку Отчеты_Продажи. В ней лежат три файла: Продажи_Январь.xlsx, Продажи_Февраль.xlsx и Продажи_Март.xlsx. В каждом файле одна таблица с четырьмя столбцами: Дата, Товар, Количество, Сумма. В январе 4 строки, в феврале 5 строк, в марте 3 строки. Ожидаемый итог после объединения — 12 строк.

Но в подпапке Архив лежит еще файл Продажи_Тест.xlsx с той же структурой и 2 строками. Если подключить папку и не проверить Folder Path, Power Query увидит и его тоже. Тогда итог станет 14 строк, и на первый взгляд ошибка будет незаметной: структура верная, явных сбоев нет, просто в объединение попали лишние данные. Именно поэтому контроль количества строк нужен не как формальность, а как способ быстро поймать логическую ошибку.

В этом примере разумная проверка выглядит так: после фильтра по пути и расширению вы убеждаетесь, что в списке осталось 3 файла. После объединения проверяете, что строк 12. Затем смотрите типы: Дата — дата, Товар — текст, Количество и Сумма — числа. Если столбец Сумма определился как текст, исправляете тип до загрузки результата. Практический итог такой настройки — при добавлении файла Продажи_Апрель.xlsx вы не повторяете объединение вручную, а просто обновляете запрос и ждете, что к имеющимся 12 строкам добавятся строки апреля.

Проверка типов, ошибок и обновление результата

Проверка типов данных — последний шаг, который нельзя пропускать. Power Query часто добавляет его автоматически, ориентируясь на пример файла. Это удобно, но не безошибочно: если в одном месяце сумма была числом, а в другом в столбец попал текстовый комментарий, типы начнут конфликтовать. Поэтому после объединения просмотрите шаги и убедитесь, что автоматическая смена типов не ломает таблицу.

Если один из файлов не соответствует образцу, например в нем другое имя таблицы или отсутствует ожидаемый столбец, вы можете увидеть ошибки. Опция пропуска файлов с ошибками иногда помогает быстро получить рабочий результат, но это не «исправление», а временное исключение проблемных файлов из итога. После такого шага объединение будет неполным, поэтому обязательно проверьте, какие файлы выпали, и сравните фактическое число строк с ожидаемым.

Обновление дальше становится простым: новые однотипные файлы добавляются в ту же папку, затем вы обновляете запрос. Если итог перестал совпадать с ожиданием, сначала проверяйте не формулы, а состав файлов в списке источника, путь к папке, расширения и последний шаг с типами. В регулярной работе именно эти четыре точки чаще всего объясняют, почему вчера все работало, а сегодня результат изменился.

Если вы выбираете обучение по теме, смотрите не на общее обещание «научим Power Query», а на три вещи: есть ли в программе базовый импорт, очистка данных и объединение таблиц; дадут ли вам самостоятельное задание с папкой однотипных файлов; кто и как проверит, что вы отследили типы, ошибки и контроль количества строк. В сохраненной программе курса Power BI и Power Query у SF Education указаны базовый импорт данных, очистка данных, практика «импортировать, обработать, выгрузить» и работа с объединением таблиц и изменением структуры данных — это как раз те темы, которые нужны для такого сценария.