Power Query в Excel: 7 приёмов, которые экономят часы рутины

• Почему Power Query экономит время
• Какой сценарий выбрать
• Приём 1. Объединение файлов из папки
• Приём 2. Очистка текста
• Приём 3. Типы данных
• Приём 4. Дубликаты
• Приём 5. Столбцы
• Приём 6. Merge и Append
• Приём 7. Параметры
• Что происходит при обновлении запроса
• Когда Power Query не решит задачу
• Следующий шаг

Почему Power Query экономит время

Большинство офисной рутины в Excel — это одни и те же действия над одними и теми же выгрузками: убрать пробелы, привести даты к нормальному виду, склеить файлы за несколько месяцев, удалить дубликаты, подтянуть справочник. Каждый месяц всё повторяется заново вручную.

Power Query устроен иначе. Вы один раз показываете ему, что нужно сделать с данными, а он запоминает эти действия как последовательность шагов. Когда источник меняется — пришёл новый файл, добавились строки — вы не переделываете работу заново, а нажимаете «Обновить», и все шаги применяются к свежим данным автоматически.

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

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

Источник, применённые шаги, загрузка и обновление

В основе любого запроса Power Query — четыре звена:

1. Источник — откуда берутся данные: таблица, диапазон, файл, папка с файлами, база.
2. Применённые шаги — список преобразований, которые вы задали: очистка, типы, объединение, фильтры. Они хранятся справа в редакторе и выполняются по порядку.
3. Загрузка результата — готовая таблица возвращается на лист Excel или в модель данных.
4. Обновление — при изменении источника те же применённые шаги повторяются к новым данным.

Ключевая мысль: источник может меняться, а шаги остаются. Именно поэтому настроенный один раз запрос экономит часы при каждой следующей выгрузке.

Что Power Query делает — и чего не делает

Power Query автоматизирует подготовку данных, но не исправляет методологию. Он повторит ваши шаги — и хорошие, и ошибочные.

Если в источнике каждый месяц меняются заголовки и набор столбцов, никакой запрос это не спасёт. Базовую ручную подготовку имеет смысл понимать до автоматизации — она разобрана в материале очистка таблицы перед анализом.

Какой сценарий выбрать

Семь приёмов ниже решают разные задачи. Выберите по своей проблеме и переходите сразу к нужному разделу.

Каждый приём разобран по одной схеме: какую задачу решает, что меняется в данных, какой шаг появляется в «Применённых шагах», что произойдёт при обновлении и где границы.

Приём 1. Объединение файлов из папки

Задача. Каждый период вам присылают однотипные файлы — продажи по месяцам, выгрузки по филиалам — и вы вручную копируете их в одну таблицу. Power Query собирает всю папку в один обновляемый источник.

Что меняется в данных. Power Query берёт один файл как образец, применяет к нему преобразования и распространяет ту же логику на все остальные файлы папки. На выходе — единая таблица из всех файлов сразу.

Какой шаг появляется в «Применённых шагах». Последовательность примерно такая: «Источник» (папка), фильтрация файлов, «Объединить файлы» (преобразование по образцу), «Изменён тип», загрузка.

Границы. Не ждите, что Power Query объединит «любые» файлы — он объединит совместимые. Надёжнее всего объединяются файлы с одинаковой схемой. Если заголовки или набор столбцов различаются, запрос может завершиться ошибкой, заполнить часть полей значениями null либо не включить неожиданные столбцы в результат. Поэтому при регулярной сборке полезно зафиксировать эталонную структуру выгрузки и сверять с ней новые файлы. Изменения преобразования файла-образца применяются ко всем файлам папки, поэтому такие шаги нужно проверять на нескольких выгрузках.

Требования к файлам

Совместимость файлов важнее их аккуратного оформления:

Что происходит при обновлении

Новый файл за следующий период с той же структурой добавится автоматически. Структурно другой файл — повод для ошибки или неполного результата. Поэтому при регулярной сборке полезно зафиксировать эталонную структуру выгрузки и сверять с ней новые файлы.

Приём 2. Очистка текста, пробелов и служебных символов

Задача. В выгрузках постоянно встречаются лишние пробелы по краям, неразрывные пробелы внутри названий, невидимые управляющие символы. На листе это решается формулами заново при каждой выгрузке — в Power Query очистка переносится на этап загрузки и повторяется сама.

Что меняется в данных. Три базовые операции решают три разные задачи:

• «Обрезать» (Trim) — убирает пробелы по краям значения;
• «Очистить» (Clean) — удаляет непечатаемые управляющие символы;
• «Заменить значения» (Replace Values) — точечно убирает конкретный мусор.

Отдельный частый случай — неразрывный пробел (символ 160). Обычное «Обрезать» его не убирает, для него нужна явная замена через «Заменить значения».

Важное отличие от листа: «Обрезать» в Power Query убирает пробелы только по краям и не схлопывает двойные пробелы внутри строки — в отличие от функции СЖПРОБЕЛЫ на листе. Для повторяющихся внутренних пробелов может потребоваться несколько замен либо более устойчивое преобразование через разделение и повторное объединение текста. Подробно ручные способы для листа разобраны в статье как убрать лишние пробелы в Excel.

Какой шаг появляется в «Применённых шагах». Обычно это «Обрезанный текст», «Очищенный текст» и «Заменённое значение» — по одному шагу на операцию.

Что произойдёт при обновлении. Те же правила очистки применятся к новой выгрузке. Если придёт новый вид мусора (другой невидимый символ), его нужно будет добавить отдельной заменой.

Границы. Основной путь очистки — через интерфейс, без кода: интерфейсных команд обычно достаточно. Если нужно «оставить только цифры или буквы», для этого есть функция Text.Select; при работе с ней учитывайте, что значения в столбце могут быть пустыми (null).

Trim, Clean и замена значений

Главное — не путать null (отсутствие значения) и пустую строку (текст нулевой длины): для Power Query это разные вещи, и об этом подробнее в следующем приёме.

Приём 3. Типы данных, null, пустые строки и нули

Задача. Самая частая причина, почему «ничего не считается»: числа лежат как текст, а даты — как строки. Power Query позволяет явно задать тип каждого столбца, и тогда расчёты, фильтры по периодам и сортировка начинают работать правильно.

Ошибки типов часто остаются незаметными до расчёта или объединения таблиц: значения выглядят правильно, но Power Query обрабатывает их как текст.

Что меняется в данных. На сквозном примере таблицы заказов:

Какой шаг появляется в «Применённых шагах». Прежде всего «Изменённый тип» (Changed Type). Иногда — «Заменённое значение». Шаг «Удалены ошибки» (Removed Errors) применяйте осознанно: он скрывает проблему, а не решает её.

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

Границы. Достаточно одного практического примера и понимания порядка шагов — разбирать все типы Power Query по отдельности здесь не нужно.

Где ставить шаг изменения типа

Порядок имеет значение. Разумная последовательность:

1. базовая очистка и нормализация текста;
2. осмысленное изменение типов;
3. расчёты, Merge/Append;
4. загрузка.

Если поменять тип до очистки, «грязное» значение может сразу дать ошибку преобразования. Сначала приведите данные в порядок, потом типизируйте.

Приём 4. Дубликаты и контроль уникальности

Задача. Убрать повторы или, наоборот, посчитать, сколько раз встречается значение. Power Query делает обе операции повторяемыми, но важно не путать их между собой.

Что меняется в данных. Результат напрямую зависит от выбранных столбцов:

• дубль всей строки и дубль ключа — это разные проверки;
• «Удалить дубликаты» и «Группировать по» (Group By) — не одно и то же;
• если важно сохранить конкретную запись (например, самую новую), одного удаления дубликатов недостаточно: сначала задайте явное правило выбора через группировку или индекс.

Подробный ручной разбор для листа — в статье как удалить дубликаты в Excel. Если задача в том, чтобы свести несколько списков и проконтролировать уникальность, пригодится материал объединить списки без дублей.

Какой шаг появляется в «Применённых шагах». «Удалены дубликаты» (Removed Duplicates) либо «Сгруппированные строки» (Grouped Rows).

Что произойдёт при обновлении. Та же логика выбора столбцов применится к свежим данным. Если в новой выгрузке ключ определяется иначе, результат изменится — поэтому правило дубля должно быть осознанным.

Границы. Удаление дублей может потерять нужные строки, если ключ выбран неверно. Один клиент с двумя заказами — не дубль в отчёте продаж, но может быть дублем в справочнике клиентов.

Удалить дубли или посчитать повторы

Приём 5. Разделение и объединение столбцов

Задача. Один столбец нужно разбить на несколько (ФИО, адрес, составной код) или, наоборот, собрать несколько в один. Power Query делает и split, и combine повторяемыми шагами.

Что меняется в данных. При разделении вы выбираете разделитель и число вхождений: по каждому, по самому левому или по самому правому. При объединении — наоборот, собираете несколько столбцов в один с нужным разделителем. Подробный ручной сценарий разбиения — в статье как разделить текст по столбцам.

Какой шаг появляется в «Применённых шагах». «Разделить столбец по разделителю» (Split Column by Delimiter), «Объединённые столбцы» (Merged Columns), при необходимости «Дублированный столбец».

Что произойдёт при обновлении. Операция повторится — но только если новый текст следует тем же правилам. Изменилась структура текста — результат «поедет».

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

Когда структура текста нестабильна

Разделение надёжно, только когда разделитель и число частей предсказуемы. Адрес с разным числом запятых, ФИО без отчества, код переменной длины — типичные источники неверных столбцов. В таких случаях сохраняйте исходный столбец, чтобы было к чему вернуться.

Приём 6. Merge и Append: добавить столбцы или добавить строки

Это приём, где чаще всего путаются, поэтому начнём с жёсткого разграничения.

• «Объединить запросы» (Merge) — добавляет столбцы из другой таблицы по ключу. Это устойчивая замена ВПР/XLOOKUP в повторяемой подготовке данных.
• «Добавить запросы» (Append) — добавляет строки одной таблицы под строки другой. Append не выполняет поиск по ключу и не подставляет значения из другой таблицы. Для такой задачи используют Merge.

Что меняется в данных. При Merge нужно выбрать тип соединения (например, «Левое внешнее»), а после объединения появляется вложенная таблица, которую нужно развернуть (Expand), оставив нужные столбцы. При Append таблицы складываются по вертикали: для предсказуемого результата у них должны совпадать названия и смысл столбцов. Если наборы различаются, Power Query создаст общий набор полей, а отсутствующие значения заполнит null, что обычно требует дополнительной проверки.

Какой шаг появляется в «Применённых шагах». «Объединённые запросы» (Merged Queries) и «Развёрнутый столбец таблицы» (Expanded Table Column) — для Merge; «Добавленный запрос» (Appended Query) — для Append.

Что произойдёт при обновлении. Связь и сложение повторятся. Для Merge критично, чтобы ключи сохранили тип и оставались согласованными; для Append — чтобы у новых данных была та же структура столбцов.

Границы. У Merge возможны отсутствующие и множественные совпадения: их нужно контролировать.

Когда Merge, когда Append

• Merge — когда нужно связать две разные таблицы: продажи и справочник товаров, заказы и справочник клиентов.
• Append — когда нужно продолжить один и тот же список: январь под февралём, филиал под филиалом.

Простой ориентир: Merge — это «добавить столбцы со стороны», Append — это «дописать строки снизу».

Что проверить после Merge

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

Приём 7. Параметры и изменяемые источники

Задача. Источник переезжает: меняется путь к папке, имя файла, период. Чтобы не править запрос вручную каждый раз, путь или фильтр выносят в отдельный параметр.

Что меняется в данных. Параметр создаётся в редакторе Power Query через «Главная → Управление параметрами → Новый параметр» и хранится отдельно от запросов. Затем его можно подставить как значение пути, например в Folder.Files. Меняете значение параметра — меняется источник, а шаги остаются прежними.

Короткий пример, как путь к папке берётся из параметра:

let Source = Folder.Files(ПутьКПапке) in Source

Какой шаг появляется в «Применённых шагах». Отдельного «шага параметра» нет — параметр живёт как самостоятельный объект и используется внутри шага «Источник».

Что произойдёт при обновлении. Параметры не «спрашивают ввод» при каждом обновлении. Запрос берёт текущее значение параметра и применяет шаги к нему.

Границы. Расположение команд может отличаться в зависимости от версии Excel. Надёжнее создавать и изменять параметры непосредственно в редакторе Power Query. И главное: параметр меняет значение в запросе, но не делает несовместимые файлы совместимыми.

Что параметр меняет, а что нет

Параметр удобно использовать, чтобы переключить папку или источник без правки кода. Но если у новой папки другая структура файлов, параметр не спасёт — это уже задача согласования схемы из Приёма 1, а не смены пути.

Что происходит при обновлении запроса

Когда вы нажимаете «Обновить», Power Query заново выполняет все «Применённые шаги» к текущему состоянию источника. Новые строки подтянутся, очистка и типы применятся, объединения повторятся.

Что обновляется без проблем:

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

Что может сломать обновление:

• переименовали или убрали столбец, на который опираются шаги;
• сменилась структура файла-образца в папке;
• источник физически переехал, а путь не вынесен в параметр.

Чек-лист перед тем, как доверить отчёт обновлению: структура источника стабильна, типы заданы осознанно, ключи для Merge согласованы, путь при необходимости параметризован. Если структура источника пока «гуляет», вернитесь к подготовке таблицы — подготовке таблицы для сводной и Power Query.

Когда Power Query не решит задачу

Power Query — мощный инструмент подготовки данных, но у него есть граница. Он не заменяет правила учёта, архитектуру отчётности и контроль качества данных.

Если файлы приходят хаотично от разных отделов, с разными правилами заполнения и без согласованной структуры, никакой запрос не сделает из них корректный отчёт — сначала нужно договориться о структуре источника. И когда отчётность перерастает одну таблицу и несколько источников, стоит честно выбрать инструмент: остаётесь ли вы в Excel и Power Query или переходите в BI. Этот выбор разобран в материале Excel против BI.

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

Следующий шаг

Power Query особенно полезен, когда базовые правила подготовки данных уже понятны: как убрать лишние символы, привести типы, проверить дубликаты и подготовить таблицу к анализу.

От ручной очистки — к первым запросам Power Query. В мини-курсе Deskardo вы отработаете базовую очистку и подготовку таблиц, а затем соберёте первые повторяемые шаги в Power Query. Курс подходит как основа перед более глубоким изучением инструмента.