Power Query в Excel: что это, где найти и как работать

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

Что такое Power Query и зачем он нужен?

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

Power Query умеет:

• подключаться к файлам Excel и CSV, папкам, базам данных, веб-страницам и другим источникам;
• удалять пустые строки, дубли, лишние столбцы и пробелы;
• менять типы данных, разделять и объединять столбцы;
• объединять таблицы по ключу — как ВПР, но для всей таблицы сразу;
• собирать в одну таблицу все файлы из папки;
• разворачивать таблицы из «широкого» формата в «длинный», удобный для сводных таблиц.

Где найти Power Query в Excel?

В Excel 2016, 2019, 2021 и Microsoft 365 Power Query встроен: откройте вкладку «Данные» и найдите группу «Получить и преобразовать данные». Чтобы начать работу с таблицей на листе, выделите её и нажмите «Из таблицы/диапазона» — откроется редактор Power Query. Для Excel 2010 и 2013 Power Query устанавливался отдельной бесплатной надстройкой. В Excel для Mac из Microsoft 365 Power Query тоже есть, но часть источников и функций доступна только в Windows-версии.

Пример 1. Как очистить выгрузку от пустых строк и лишних пробелов

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

1. Загрузите таблицу. Выделите диапазон и нажмите «Данные» → «Из таблицы/диапазона».
2. Удалите пустые строки. «Главная» → «Удалить строки» → «Удалить пустые строки».
3. Уберите пробелы. Выделите столбец с названиями → «Преобразование» → «Формат» → «Усечь».
4. Исправьте типы. Щёлкните по значку типа в заголовке столбца с суммой и выберите «Десятичное число», для дат — «Дата».
5. Выгрузите результат. «Главная» → «Закрыть и загрузить» — чистая таблица появится на новом листе.

В следующем месяце замените исходные данные и нажмите «Данные» → «Обновить всё»: все пять шагов выполнятся автоматически.

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

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

1. Сложите файлы в одну папку.
2. «Данные» → «Получить данные» → «Из файла» → «Из папки» и укажите путь к папке.
3. В окне со списком файлов нажмите «Объединить» → «Объединить и преобразовать данные» и выберите лист или таблицу-образец.
4. Power Query сложит строки всех файлов и добавит столбец с именем файла — по нему удобно определить месяц.
5. Нажмите «Закрыть и загрузить». Когда в папке появится новый файл, достаточно обновить запрос.

Пример 3. Как развернуть таблицу с месяцами в столбцах

Задача: бюджет хранится в «широком» виде — статьи в строках, месяцы в столбцах. Для сводной таблицы и графиков нужен «длинный» формат: статья, месяц, сумма.

1. Загрузите таблицу в Power Query.
2. Выделите столбец со статьями.
3. «Преобразование» → «Отменить свёртывание других столбцов».
4. Переименуйте получившиеся столбцы «Атрибут» и «Значение» в «Месяц» и «Сумма».

Теперь по таблице можно строить сводные и графики по месяцам. Так же Power Query записывает этот шаг на языке M:

= Table.UnpivotOtherColumns(Источник, {"Статья"}, "Месяц", "Сумма")

Как заменить ВПР объединением запросов?

Если нужно подтянуть данные из справочника, например цену товара по артикулу, в Power Query используют «Главная» → «Объединить запросы». Выберите две таблицы, отметьте столбцы-ключи и вид соединения, затем разверните нужные столбцы из второй таблицы. В отличие от ВПР, формулы не нужно протягивать, а при обновлении данных объединение пересчитается само. Виды соединения в Power Query работают так же, как JOIN в SQL: внутреннее, левое внешнее, полное внешнее.

Чем Power Query связан с Power BI?

В Power BI встроен тот же Power Query: интерфейс редактора и язык M совпадают. Поэтому запросы, которые вы научились строить в Excel, переносятся в Power BI без переучивания, а очищенные данные сразу превращаются в дашборды.

Частые вопросы