Классика отчётности: каждый месяц вам присылают 10 одинаковых Excel-файлов. И каждый месяц вы делаете одно и то же — копируете, вставляете, проверяете “почему пропали строки”, ругаетесь на пробелы и форматы дат.
В этой статье вы настроите сбор данных из папки через Power Query так, чтобы дальше отчёт обновлялся в один клик, без ручной склейки.
Внутри:
- как подготовить файлы и папку, чтобы PQ не “капризничал”;
- как объединить всё в одну таблицу и добавить “служебные” поля (имя файла/дата);
- как защититься от “лишнего столбца”, “пустого файла” и прочих сюрпризов;
- чек-лист и типовые ошибки.
Аналитика без боли #2
(серия для аналитиков и тех, кто работает с данными: Excel / Power Query / BI / SQL)
Что получим на выходе
На выходе будет одна “чистая” таблица, собранная из всех файлов в папке, плюс понятный процесс эксплуатации:
- кладёте новый файл в папку;
- нажимаете “Обновить”;
- таблица пересобирается сама.
Дополнительно (по желанию):
- колонка source_file — из какого файла пришла строка;
- колонка load_date или report_month — чтобы удобно фильтровать в отчёте;
- базовая защита от “не того файла” и “пустого файла”
Пошаговый разбор (8 шагов)
Шаг 1. Подготовьте папку и правила именования
Действие: создайте папку, например:
D:\Reports\sales_monthly\raw\
Сразу договоримся о минимальной дисциплине:
- Все файлы — одного формата (лучше .xlsx).
- В каждом файле есть один лист с данными (или одинаковое имя листа).
- Заголовки столбцов одинаковые по смыслу (лучше — один в один).
Зачем так:
Power Query “прощает” многое, но если заголовки и структура гуляют, вы получите либо ошибки, либо тихую потерю данных.
Шаг 2. Мини-данные (как должны выглядеть файлы)
Сделаем пример: каждый файл — продажи за месяц.
Файл sales_2025_01.xlsx (лист data)
Файл sales_2025_02.xlsx (лист data)
Три ключевых момента:
- order_id — число;
- order_date — дата;
- revenue — число (без “руб.” в ячейке).
Шаг 3. Подключение “Из папки”
Действие в Excel:
- Вкладка Данные → Получить данные → Из файла → Из папки
- Выберите папку ...\raw\
- Нажмите Преобразовать данные (не “Загрузить”)
Вы попадёте в Power Query с таблицей файлов: имя, расширение, дата изменения и т.п.
Зачем так:
“Из папки” — это основной паттерн для автоматизации: дальше всё работает на новых файлах без правки логики.
Шаг 4. Отфильтруйте мусор: временные файлы и лишние расширения
Действие: в списке файлов:
- оставьте только .xlsx (или что вам нужно);
- отфильтруйте временные файлы Excel, которые начинаются с ~$.
В PQ это обычно делается фильтром по колонке Extension и/или Name.
Зачем так:
Иначе вы периодически будете ловить “непонятные ошибки” из-за служебных файлов, которые Excel создаёт сам.
Шаг 5. Объединение файлов
В Power Query есть кнопка “Объединить файлы” (Combine).
Действие:
- Нажмите Объединить → Объединить и преобразовать
- Выберите нужный лист/таблицу внутри файла (например, лист data)
- PQ создаст запросы-помощники и итоговую таблицу.
Что получится:
Одна таблица, где строки из всех файлов уже объединены.
Зачем так:
PQ автоматически строит “функцию чтения файла” и применяет её ко всем файлам в папке. Это и есть автоматизация.
Шаг 6. Приведите типы данных (чтобы “2025-01-03” не стал текстом)
Это шаг, который пропускают — и потом “почему BI не группирует по датам”.
Действие:
- order_id → целое число
- order_date → дата
- revenue → число (десятичное/целое — по данным)
В PQ: выделяете колонку → Тип данных.
Зачем так:
Если тип “текст”, вы получите:
- невозможность нормальной агрегации по датам;
- сортировку “как строка” (1, 10, 11, 2…);
- ошибки в дальнейших расчётах.
Шаг 7. Добавьте “служебные” колонки: имя файла и месяц отчёта
Это превращает “просто склейку” в рабочий инструмент аналитика.
Вариант А: добавить имя файла
Если PQ уже даёт колонку Source.Name или аналог — переименуйте её в source_file.
Вариант Б: извлечь месяц из имени файла
Если файлы названы как sales_2025_02.xlsx, можно достать 2025_02.
Псевдо-логика:
- берём имя файла без расширения;
- вытаскиваем последние 7 символов YYYY_MM;
- создаём report_month.
Зачем так:
В отчёте удобно фильтровать “какой файл дал какие строки”, быстро находить проблемные выгрузки и строить динамику.
Шаг 8. Загрузка в Excel и обновление в один клик
Действие:
- Нажмите Закрыть и загрузить → в таблицу на лист
- Проверьте: таблица появилась, данные корректны
- Дальше эксплуатация простая: добавили новый файл в папку → Данные → Обновить всё
Зачем так:
Вы получаете повторяемую процедуру без ручной работы: “папка — источник истины”.
Артефакт пользы: чек-лист “Сбор из папки через Power Query” (сохрани)
- Все файлы в одной папке raw (без вложенных хаотичных подпапок).
- Одинаковая структура: один лист/таблица с данными во всех файлах.
- Заголовки одинаковые (лучше копировать шаблон).
- Фильтр на .xlsx и исключение ~$*.
- Объединение через “Объединить файлы”, а не ручные append’ы.
- Типы данных заданы явно: дата = дата, число = число.
- Добавлены source_file и/или report_month (для контроля и фильтров).
- Проверка на пустые/ошибочные строки после объединения.
- Выходная таблица — в “умную таблицу” Excel (удобно для сводных/BI).
- Процедура обновления закреплена: “закинул файл → Обновить всё”.
- Запрос назван понятно: pq_sales_from_folder.
- Папка raw не используется для ручного редактирования файлов “на месте” (лучше заменять файлы целиком).
Типовые ошибки и как не сломать (5)
- В одном файле другой заголовок (“OrderDate” вместо “order_date”).
Исправление: стандартизируйте заголовки или сделайте шаг переименования в PQ. - В одном файле дата записана текстом (“03.01.2025”), в другом как дата.
Исправление: приводите типы после объединения, при необходимости — преобразование текста в дату. - В папке лежит “старый файл” и “новый файл”, вы считаете оба.
Исправление: добавьте фильтр по имени/периоду или храните архив отдельно (archive). - Случайно попал пустой файл.
Исправление: фильтр “размер файла > 0” и/или обработка ошибок функции чтения. - Excel временные файлы ~$ ломают объединение.
Исправление: обязательное исключение по имени.
Как использовать в работе аналитика (2 сценария)
- Ежемесячная отчётность по продажам/инцидентам/обращениям.
Все выгрузки складываются в папку, отчёт обновляется, сводные/дашборд строятся на одной таблице. - Сбор данных от филиалов.
10 филиалов — 10 файлов. source_file помогает быстро найти “кто прислал криво” и не спорить на встрече.
Вариации/улучшения (4 идеи)
- Добавить папки raw / processed / archive и автоматически обновлять только raw.
- Делать контроль качества: “количество строк по файлам” (группа по source_file).
- Нормализовать значения (Trim/Lower) для категорий, городов и т.п.
- Добавить справочник соответствий (“СПб” → “Санкт-Петербург”) прямо в PQ.
Проверка результата (4 проверки)
- Сверьте строки: сумма строк по всем файлам = строкам в итоговой таблице.
- Проверьте типы: дата реально дата (группировка по месяцам должна работать).
- Группа по source_file: нет ли файла, который дал “0 строк” или в 10 раз больше остальных.
- Добавьте “новый файл” в папку и убедитесь, что обновление подхватывает его без правок запроса.
Финал
Это была часть серии “Аналитика без боли”: вы настроили сбор данных из папки через Power Query и перестали заниматься ручной склейкой.
Если статья сэкономит вам хотя бы один отчётный вечер — сохраните и перешлите коллеге, который каждый месяц делает “копировать-вставить” и считает это нормой.