Power Query

Power Query — это встроенный компонент программных продуктов Microsoft (Excel, Power BI, Azure Data Factory, а также отдельный продукт Power Query for Desktop), предназначенный для извлечения, трансформации и загрузки данных (ETL-процесс) из различных источников в аналитическую среду. Power Query предоставляет графический интерфейс и язык формул M для выполнения операций очистки, объединения, фильтрации и подготовки данных без написания кода на традиционных языках программирования.

История

Power Query был впервые представлен компанией Microsoft в 2013 году как надстройка для Excel 2010 и Excel 2013 под названием «Power Query for Excel». В 2015 году Microsoft включила Power Query в состав Power BI — облачного сервиса для бизнес-аналитики. С выходом Excel 2016 Power Query стал встроенным компонентом программы (раздел «Получить и преобразовать данные»). В последующих версиях Excel (2019, Microsoft 365) функциональность была расширена: добавилась поддержка более 100 различных источников данных, улучшен алгоритм автоматического обнаружения типов данных и расширена библиотека встроенных преобразований. В 2020 году Microsoft выпустила отдельную версию Power Query for Desktop (Power Query Desktop), которая доступна бесплатно и позволяет подготавливать данные без привязки к Power BI или Excel. На 2025 год Power Query является центральным инструментом для подготовки данных в экосистеме Microsoft Power Platform.

Классификация

Power Query классифицируется по нескольким критериям:

По среде выполнения

• Встроенный в Excel — используется для разовой или регулярной загрузки данных в таблицы Excel.
• Встроенный в Power BI Desktop — применяется для создания этапов преобразования данных в модели Power BI.
• В Power BI Service — в некоторых сценариях (например, при использовании шлюзов данных) поддерживает обновление запросов в облаке.
• Azure Data Factory — используется в конвейерах обработки данных в облачной платформе Azure.
• Power Query Online — версия, работающая в браузере без установки (доступна в Power BI Service и Azure).

По типу операций

• Операции извлечения — подключение к источникам (файлы, базы данных, веб-страницы, OData, API).
• Операции трансформации — очистка, фильтрация, объединение, разделение столбцов, изменение типов данных, группировка.
• Операции загрузки — вывод результатов в таблицу Excel, модель данных Power BI или другой целевой формат.

Устройство и характеристики

Интерфейс

Power Query предоставляет два основных способа работы:

• Графический редактор запросов — окно Power Query Editor, в котором пользователь выбирает действия из контекстного меню или ленты инструментов. Каждое действие автоматически записывается в виде шага на языке M.
• Расширенный редактор — текстовое окно для написания и редактирования формул на языке M напрямую.

Язык формул M

Основой Power Query является функциональный язык M (M Language), который нечувствителен к регистру. Запросы состоят из шагов (steps), каждый из которых ссылается на предыдущий. Пример простого запроса: `` let Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], Filtered = Table.SelectRows(Source, each [Колонка1] > 100) in Filtered `` Язык M поддерживает работу с таблицами, записями, списками, числами, датами, временем и пользовательскими функциями.

Основные источники данных

Power Query поддерживает более 150 различных источников, включая:

• Файлы — Excel, CSV, JSON, XML, PDF, Text.
• Базы данных — SQL Server, Oracle, MySQL, PostgreSQL, SAP HANA, Azure SQL Database.
• Облачные сервисы — SharePoint, Salesforce, Dynamics 365, Google Analytics, Facebook (организация признана экстремистской и запрещена в РФ), Azure Blob Storage.
• Веб-страницы — таблицы и списки с HTML-страниц.
• OData и REST API — доступ к любым API, поддерживающим OData или REST.

Ограничения

• Максимальный размер загружаемых данных в Excel 2019 — около 2 млрд строк (ограничение листа Excel). В Power BI — зависит от объёма оперативной памяти.
• Скорость выполнения запросов в основном зависит от производительности ПК и оптимизации шагов.
• Язык M не является полноценным промышленным языком (например, не поддерживает параллельные вычисления без сторонних инструментов).

Применение

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

• Подготовки отчётов — преобразование сырых данных (например, выгрузок из 1С или SAP) в структурированные таблицы для построения сводок и диаграмм.
• Автоматизации рутинной очистки — удаление пустых строк, замена значений, приведение к единому формату дат.
• Объединения данных из разных систем — слияние таблиц из CRM, ERP и Excel-файлов в единую модель.
• Создания динамических моделей — настройка запросов с параметрами (например, выбор диапазона дат) для интерактивных отчётов.
• Обновления данных по расписанию — через Power BI Service или Excel Online с использованием шлюзов данных.

Примеры использования

Пример 1: Подготовка отчёта по продажам

Исходные данные: несколько Excel-файлов с продажами за каждый месяц. Power Query может:

1. Считать все файлы из папки.
2. Объединить их в одну таблицу.
3. Удалить дубликаты.
4. Изменить типы данных столбцов.
5. Загрузить результат в модель Power BI для построения дашборда.

Пример 2: Очистка данных из CRM

Исходные данные: таблица из Dynamics 365 с нестандартными форматами дат и числами с пробелами в качестве разделителей. Power Query автоматически:

1. Определяет и стандартизирует даты.
2. Удаляет пробелы из числовых столбцов.
3. Разделяет ФИО на отдельные столбцы.
4. Исключает строки с неполными данными.

Интересные факты

• Название «Power Query» буквально означает «мощный запрос»; в русскоязычной документации иногда используется термин «Получить и преобразовать данные».
• В Power Query встроен шаблон «История запросов», который позволяет откатиться на любой шаг обработки.
• Язык M включает множество встроенных функций, эквивалентных функциям DAX (Data Analysis Expressions), что упрощает миграцию между Power Query и DAX.
• Power Query может подключаться к таким источникам, как веб-камеры или датчики IoT через REST API.
• В Excel Online (веб-версия) Power Query доступен в ограниченном режиме — только для просмотра существующих запросов, но не для создания новых.

Критика

Основные недостатки Power Query, отмечаемые пользователями и экспертами:

• Сложность отладки — при большом количестве шагов (более 20) становится трудно отслеживать логику преобразований.
• Производительность — запросы к крупным источникам (более 1 млн строк) без оптимизации могут выполняться медленно.
• Закрытость экосистемы — Power Query не интегрируется напрямую с не-Microsoft продуктами (например, Google BigQuery, Snowflake) без специальных коннекторов.
• Ограниченный контроль над M-кодом — в графическом интерфейсе невозможно настроить сложное ветвление или рекурсию без использования расширенного редактора.

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

Источники

• Документация Microsoft: «Power Query Overview» (learn.microsoft.com)
• Книга Кена Пулса «M is for Data Monkey: A Guide to the M Language in Excel Power Query» (2014)
• Статья Майкла Александра «Power Query for Everyone» (2020)
• Официальный блог Microsoft Power BI: «Power Query Updates» (2022–2025)
• Справочник по функциям языка M на сайте Microsoft Learn