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

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

В этой статье собраны 7 приёмов Power Query, которые закрывают большинство базовых задач и реально экономят часы.

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

Краткий обзор: какие приёмы разберём

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

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

Шаги в Excel

1. На вкладке Данные выберите команду Получить данные → Из файла → Из папки.
2. Укажите папку, где лежат файлы с одинаковой структурой (одинаковые столбцы).
3. Нажмите Преобразовать данные, чтобы открыть редактор Power Query.
4. При необходимости отфильтруйте лишние файлы (по расширению, имени).
5. Используйте кнопку Объединить, чтобы собрать данные в одну таблицу.
6. Загрузите результат в лист или в модель данных.

Теперь при появлении нового файла в папке достаточно нажать «Обновить», и таблица в Excel автоматически подхватит новые данные.

Мини-сравнение: ручной способ vs Power Query

Приём 2. Автоматическая очистка текста и пробелов

Лишние пробелы, невидимые символы и «грязный» текст ломают сводные, поиск и сравнение. Раньше всё это приходилось лечить формулами и инструментами на листе (например, через функцию ПРОПНАЧ и замену). Power Query позволяет чистить данные ещё на этапе загрузки.

Базовые шаги очистки в Power Query

1. Загрузите таблицу в Power Query через Данные → Из таблицы/диапазона.
2. Выберите текстовый столбец и используйте команды на вкладке Преобразование:
   • Формат → Обрезать — аналог удаления лишних пробелов по краям.
   • Формат → Очистить — удаляет непечатные символы.
   • Заменить значения… — точечная замена «мусора» (скобки, дефисы, +7).

Если нужно оставить только цифры или только буквы, можно использовать расширенный шаг с функцией Text.Select в языке M (редактор формул Power Query). Например, чтобы оставить только цифры в столбце [Телефон]:

= Table.TransformColumns(
    #"Предыдущий шаг",
    {
        "Телефон", each Text.Select(_, {"0".."9"})
    }
)

💡 Совет. Функция Text.Select работает только в редакторе формул Power Query (M-язык). Если ты пока не знаком с M, начни с базовых шагов на вкладках «Главная» и «Преобразование» — они позволяют обрезать пробелы, очищать текст и заменять символы через интерфейс, без кода.

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

Приём 3. Удаление дубликатов и подсчёт уникальных значений

В Excel уже есть инструмент «Удалить дубликаты», но он работает только один раз: если данные обновились, процедуру нужно повторять вручную. Power Query позволяет удалять дубликаты при каждом обновлении, а также считать количество повторов.

Как удалить дубликаты в Power Query

1. Загрузите диапазон или таблицу в Power Query.
2. Выделите один или несколько столбцов, по которым нужно проверять уникальность.
3. На вкладке Главная выберите команду Удалить строки → Удалить дубликаты.
4. Загрузите результат обратно в Excel.

Если нужно не только удалить повторы, но и посчитать, сколько раз встречается значение (например, сколько раз клиент сделал заказ), используйте «Группировать по»:

1. Выберите столбец с ключом (например, Клиент).
2. Команда Преобразование → Группировать по.
3. Выберите операцию Количество строк для нового столбца.

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

Приём 4. «Текст по столбцам» на стероидах

Почти у каждого в отчётах есть такие столбцы, как «ФИО», «Адрес», «Код товара + цвет + размер». В Excel это обычно решается через «Текст по столбцам». В Power Query тот же сценарий реализуется через шаг «Разделить столбец», но с более гибкими настройками и возможностью обновления.

Как разделить столбец в Power Query

1. Загрузите таблицу в Power Query.
2. Выберите нужный столбец (например, ФИО).
3. Команда Преобразование → Разделить столбец → По разделителю.
4. Укажите разделитель (пробел, запятая, тире или пользовательский символ).
5. Выберите, как делить: «По каждому вхождению», «По левому самому» или «По правому самому» разделителю.

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

Приём 5. Объединение таблиц без ВПР: Merge и Append

Ещё один типовой сценарий: есть таблица продаж, таблица справочника товаров, таблица курсов валют, таблица планов. На листе это обычно решают формулами ВПР/XLOOKUP, которые со временем начинают тормозить и ломаться.

В Power Query вместо этого используются два типа объединения:

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

Пример: добавить к продажам названия товаров из справочника

1. Загрузите в Power Query два запроса: Продажи и СправочникТоваров.
2. Выберите запрос Продажи и команду Главная → Объединить запросы.
3. В диалоге укажите:
   • верхняя таблица — Продажи, столбец с кодом товара;
   • нижняя таблица — СправочникТоваров, столбец с тем же кодом;
   • тип соединения — чаще всего Левое внешнее (берём все продажи и подтягиваем товар).
4. Разверните вложенную таблицу со справочником и выберите нужные поля (например, Наименование, Категория).

В результате получаете таблицу, которая ведёт себя как «ВПР с автоподбором диапазона»: добавились новые товары или обновился справочник — вы просто обновляете запрос.

Если вы пока решаете такие задачи формулами, посмотрите мини-справочник «Excel как язык формул» — он помогает понять, когда удобнее остаться на листе, а когда уже пора переместить «тяжёлую» логику в Power Query или BI.

Приём 6. Автоматический календарь дат для отчётов

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

Пример шагов в Power Query

1. Создайте пустой запрос: Данные → Получить данные → Из других источников → Пустой запрос.
2. Откройте Расширенный редактор и вставьте простую M-формулу для списка дат:

let
    StartDate = #date(2020, 1, 1),
    EndDate   = #date(2030, 12, 31),
    DayCount  = Duration.Days(EndDate - StartDate) + 1,
    DateList  = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Дата"}),
    WithYear  = Table.AddColumn(DateTable, "Год", each Date.Year([Дата]), Int64.Type),
    WithMonth = Table.AddColumn(WithYear, "Месяц", each Date.Month([Дата]), Int64.Type)
in
    WithMonth

Дальше можно добавить номера недель, кварталы, текстовые названия месяцев через функции Date.WeekOfYear, Date.QuarterOfYear, Date.MonthName и загрузить таблицу дат в модель данных или на лист.

Приём 7. Параметры и динамические пути к файлам

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

Простой пример: параметр пути к папке

1. В Power Query выберите меню Главная → Управление параметрами → Новый параметр.
2. Задайте имя, например ПутьКПапке, и укажите текстовое значение с фактическим путём.
3. В запросе, где используется Folder.Files("…"), замените строку пути на имя параметра:

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

Теперь, если папка с файлами переместится, вам не нужно открывать редактор кода — достаточно изменить значение параметра. 💡 Совет. Параметры можно менять прямо в Excel: на вкладке Данные выберите Получить данные → Запросы и подключения → Свойства запроса → Управление параметрами (или пункт «Управление параметрами» в меню Power Query). Изменения применяются при следующем обновлении запроса.

Тот же подход можно использовать для:

• выбора режима (черновой/боевой источник данных);
• фильтрации по дате (период отчёта);
• подстановки URL при работе с веб-источниками.

Когда Excel уже не справляется — и что дальше

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

Здесь в ход идут Power BI, модели данных и дашборды. Хороший ориентир — материалы:

• Excel против BI: когда таблицы начинают мешать
• Разработка BI-отчётов и дашбордов под бизнес

А если вы только выстраиваете поток «сырой выгрузки → аккуратная таблица → отчёт», загляните в расширенный гид «10 способов очистить таблицу перед анализом» — он хорошо сочетается с приёмами Power Query из этой статьи.