Power Query в Excel: как чистить еженедельную выгрузку записанными шагами

Задача знакома бухгалтеру и аналитику: выгрузка из учётной программы приходит каждый понедельник с теми же лишними столбцами, повторами и числами в виде текста. Справка Microsoft описывает решение в четыре этапа: подключиться к данным, преобразовать их, объединить источники и загрузить результат на лист или в модель данных^[1]. Ручная чистка отнимает час и каждый раз даёт немного разный итог.

Путь освоения Power Query в Excel короткий. Сначала поймите, какая выгрузка повторяется и что в ней приходится исправлять. Затем выберите инструмент: для регулярной обработки подходит запрос, а не формулы и не ручные правки. Освойте основы — источник, редактор и список шагов, — попробуйте собрать запрос в тренажёре и примените его к своей выгрузке. Главный риск новичка — править результат прямо на листе: при следующем обновлении запрос перезапишет таблицу.

Ключевые факты: Power Query в Excel

• Power Query записывает каждое действие с данными как шаг в разделе «Примененные шаги» и повторяет шаги при обновлении^[1].
• Лист вмещает 1 048 576 строк, поэтому длинный результат запроса загружают в модель данных^[2].
• Power Query не поддерживается в Excel 2016 и Excel 2019 для Mac^[1].
• Настраиваемый столбец с оператором & отличается от команды «Объединить столбцы» тем, что сохраняет исходные столбцы^[3].
• Справка Microsoft относит Power Query к Excel для Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016^[1].

Что такое Power Query и какие задачи он закрывает

Power Query загружает данные из источника, чистит их шагами и выгружает на лист. Что умеет Power Query, видно по редактору: справка Microsoft называет окно преобразований редактором Power Query, а каждое действие показывает в разделе «Примененные шаги»^[1]. Удалили столбец, поменяли тип, отфильтровали пустые строки — в списке появились три шага, и любой из них можно открыть, чтобы увидеть таблицу на этом этапе.

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

Запрос не трогает исходный файл: справка описывает его как новое представление исходного и неизменного источника^[1]. Ошибка в шаге не портит выгрузку из учётной программы — неудачный шаг правят или убирают в том же списке, и таблица возвращается к предыдущему состоянию.

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

Разобравшись с назначением, переходим к поиску команд — их расположение зависит от версии.

Где найти Power Query в Excel 2016–2024 и на Mac

Команды Power Query в Excel вы найдёте на вкладке «Данные» в группе получения данных^[1]. Русская справка называет эту группу «Получение & преобразование данных» — амперсанд остался от перевода, в английском интерфейсе это Get & Transform Data^[4]. Отдельно устанавливать ничего не нужно: в современных версиях для Windows инструмент встроен.

Впервые инструмент появился как бесплатная надстройка для Excel 2010 для Windows, и надстройка для Excel 2010 и 2013 объявлена нерекомендуемой летом 2019 года^[1]. Если кнопки нет или она неактивна в старой корпоративной сборке Windows, проверьте компоненты: справка требует .NET Framework не ниже 4.7.2 и среду WebView2^[1].

Набор источников зависит от выпуска. «Таблица или диапазон», «Текстовый & CSV», «Книга Excel» и «локальная папка» есть во всех выпусках для Windows, а Oracle, MySQL и PostgreSQL — только в Professional Plus и приложениях Microsoft 365 для предприятий^[5]. Импорт из PDF справка относит только к Microsoft 365^[5], поэтому на домашнем Excel 2019 такого пункта не будет.

Однако найти кнопку — полдела: запрос устроен иначе, чем обычный лист, и первый раз его лучше собрать на маленькой таблице.

Как пользоваться Power Query: первый запрос от источника до загрузки

Первый запрос Power Query создают из таблицы на листе за пять действий в редакторе. Возьмём кусок выгрузки заказов, где есть полный повтор и повтор, набранный другим регистром:

1. Щёлкните ячейку таблицы и в группе получения данных на вкладке «Данные» выберите источник «Таблица или диапазон» — откроется редактор.
2. Проверьте типы: у столбца «Сумма» нужен числовой тип, у «Фамилии» и «Имени» — текстовый.
3. Приведите «Фамилию» к одному регистру, затем выделите все столбцы и удалите дубликаты.
4. Выберите «Главная → Закрыть и загрузить», и результат ляжет на новый лист^[6].
5. Добавьте строку в исходную таблицу и обновите запрос — все шаги повторятся на новых данных.

Без шага с регистром строка «ГУСЕВ» останется в результате как отдельный клиент: справка о дубликатах предупреждает, что Power Query различает регистр^[7]. Справа в редакторе виден список шагов — щёлкните любой, чтобы проверить таблицу на этом этапе.

Преобразования в редакторе: типы, регистр и пробелы

Кнопки на вкладках редактора закрывают основные преобразования: сменить тип данных, привести текст к одному регистру, убрать лишние пробелы, заменить значения. Каждое действие добавляет шаг в конец списка, поэтому порядок важен: регистр приводят до удаления повторов, а тип «Текст» задают до склейки столбцов^[3].

Если таблица на листе не нужна и данные пойдут только в сводную, выберите «Закрыть и загрузить в…» и в окне «Импорт данных» отметьте «Добавить эти данные в модель данных»^[6]. Вернуться к запросу можно из области «Запросы и подключения»: правой кнопкой по запросу → «Изменить»^[6].

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

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

Именно поэтому две самые частые операции — удаление повторов и склейку столбцов — стоит разобрать отдельно.

Удаление дубликатов или склейка столбцов: чем отличаются команды

Удаление дубликатов в Power Query убирает строки-повторы, а объединение столбцов склеивает значения. Обе операции делаются кнопками редактора, без формул, но условия у них разные.

На вкладке Home в группе Reduce rows выберите Remove rows → Remove duplicates — так путь описан в английской справке^[7]. Выделили все столбцы — останутся уникальные сочетания; выделили один — по строке на каждое значение. Power Query не гарантирует, что из набора повторов останется первая строка^[7], поэтому не стройте на этом отчёт. В коде шаг выглядит так:

= Table.Distinct(Источник)

Функция Table.Distinct без второго аргумента проверяет все столбцы таблицы^[8].

Склейка фамилии с именем — аналог функции СЦЕПИТЬ на листе. Объединять можно только столбцы текстового типа^[3]: число или дату сначала переведите в тип «Текст». Если исходные столбцы нужны дальше, добавьте настраиваемый столбец:

= [Фамилия] & " " & [Имя]

Команда «Объединить столбцы» отличается от настраиваемого столбца тем, что исходные столбцы после неё больше недоступны^[3]. Справка советует второй вариант ещё и потому, что он защищает от ошибок при будущих обновлениях^[3].

Остаётся понять, в каких задачах инструмент лишний.

Когда Power Query не подойдёт

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

• Если выгрузку нужно поправить один раз и забыть, настройка шагов не окупится — хватит команд вкладки «Данные».
• Когда результат должен меняться при вводе каждого числа, нужны формулы: таблица-результат меняется только по команде «Обновить»^[9].
• В Excel 2016 и Excel 2019 для Mac инструмент не поддерживается^[1] — нужен Microsoft 365 для Mac или Windows.
• При задаче вроде действий с файлом, а не с данными: разослать письма или распечатать листы по списку — нужен макрос, записанный или написанный вручную.
• Если результат длиннее 1 048 576 строк, он не поместится на лист^[2] — загружайте его в модель данных и считайте мерами в модели данных Power Pivot.

Перед настройкой проверьте, повторяется ли задача: шаги окупаются со второго обновления, когда та же выгрузка приходит снова. В Excel в Интернете набор источников уже: в таблице для веб-версии отдельно названы «Таблица или диапазон» и «Файлы SharePoint»^[5]. Справка о командах предупреждает, что не каждый результат можно загрузить на лист^[9] — такие данные отправляют в модель.

В Google Таблицах этого инструмента нет: справка Microsoft называет только три приложения Excel — для Windows, Mac и Интернета^[1]. Похожую задачу там частично закрывает функция IMPORTRANGE, которая импортирует диапазон ячеек из другой таблицы после явного разрешения доступа^[10].

Свою выгрузку начните с трёх-четырёх шагов: удалить лишние столбцы, задать типы, убрать повторы. Соседние темы раздела собраны среди всех уроков тренажёра по автоматизации и коду.