Что такое Power Query в Excel: установка, запуск и работа с данными

Power Query в Excel: что это такое и зачем нужен

Power Query реализует три этапа: Extract — подключается к источнику данных; Transform — преобразует данные в редакторе запросов; Load — выгружает результат на лист Excel или в модель данных Power Pivot. Надстройка бесплатна; начиная с Excel 2016 встроена по умолчанию под именем Get & Transform («Получить и преобразовать»). Поддерживает 13 типов данных и работает без ограничений по количеству строк в запросе — в отличие от стандартного листа.

Чем Power Query отличается от стандартного Excel

Стандартный лист Excel вмещает не более 1 048 576 строк. Power Query снимает это ограничение: запрос обрабатывает данные любого объёма, а на лист попадает только итоговая выборка. Поддерживаемые форматы: XLS, CSV, JSON, XML, PDF, TXT; базы данных; облачные сервисы. Главное преимущество — при каждом обновлении источника данных все шаги преобразования воспроизводятся автоматически, без ручной работы.

Power Query и Power Pivot: в чём разница

Инструменты работают в связке: Power Query очищает и загружает данные в модель данных ThisWorkbookDataModel — Power Pivot строит поверх неё меры и отчёты. Это не конкуренты, а два последовательных звена одного пайплайна.

Как включить Power Query в Excel: зависит от версии

Способ активации Power Query в Excel зависит от версии офисного пакета. С 2016 года надстройка встроена в раздел «Данные» — скачивать ничего не нужно. В Excel 2010 и 2013 Power Query устанавливается отдельно через Microsoft Download Center.

Excel 2016, 2019, 2021 и Microsoft 365 — где найти Power Query

В Excel 2016 надстройка называется Get & Transform. Путь: вкладка «Данные» → раздел «Скачать и преобразовать» → кнопка «Создать запрос». В Excel 2019, 2021 и Microsoft 365 интерфейс обновлён: «Данные» → «Получить и преобразовать данные» → «Получить данные». Отдельную вкладку Power Query в этих версиях не ищите — весь функционал уже в разделе «Данные», ничего дополнительно устанавливать не требуется.

Excel 2010 и 2013 — скачать и установить Power Query бесплатно

Шаг 1. Определите разрядность. Откройте Файл → Справка → «О программе». Найдите строку с разрядностью: 32-bit или 64-bit — установщики для разных разрядностей несовместимы.

Шаг 2. Скачайте установщик. На странице Microsoft Download Center найдите надстройку Power Query для Excel 2013 (или 2010) и скачайте MSI-файл нужной разрядности бесплатно.

Шаг 3. Установите. Запустите MSI, следуйте инструкции установщика. После завершения перезапустите Excel.

Шаг 4. Проверьте вкладку. В ленте Excel появится отдельная вкладка Power Query.

Пользователям Excel 2010 важно знать: часть функций в этой версии недоступна, в том числе Excel.Workbook() — функция подключения к другой книге как к источнику данных. Для полноценной работы рекомендуется Excel 2013 и выше.

Редактор Power Query: интерфейс и основные элементы

Редактор Power Query открывается двойным кликом по запросу в панели «Запросы и подключения» (Данные → «Запросы и подключения»). Интерфейс включает четыре ключевые зоны:

• список запросов (слева) — для переключения между несколькими запросами;
• строка формул — отображает код на языке M для активного шага;
• область предпросмотра (по центру) — таблица с результатом текущего запроса;
• панель «Применённые шаги» (справа) — история всех преобразований.

Над предпросмотром — лента с вкладками: «Главная», «Преобразование», «Добавление столбца», «Просмотр». Закрыть редактор: кнопка «Закрыть и загрузить» или «Закрыть и загрузить в…» с выбором формата выгрузки.

Применённые шаги: история преобразований и откат

Каждое действие в редакторе Power Query фиксируется как отдельный шаг в правой панели. Каждый шаг — это строка кода на языке M, которая отображается в строке формул. Чтобы откатить преобразование, нажмите правую кнопку мыши на нужном шаге и выберите «Удалить». Исходные данные при этом не затрагиваются — редактор работает с копией, не с оригинальным файлом.

Как работать в Power Query: пошаговая инструкция

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

Шаг 1 — Подключение к источнику данных

Откройте вкладку «Данные» → «Получить данные» и выберите категорию источника:

• Файлы: XLS, CSV, JSON, XML, PDF, TXT и другие.
• Базы данных: MS SQL Server, Access, Oracle, PostgreSQL, SAP HANA, Amazon Redshift.
• Облачные службы: SharePoint, Azure, Google Analytics и другие.

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

Шаг 2 — Преобразование данных в редакторе

[IMAGE: image_8]

Базовые операции через интерфейс без написания кода:

• Изменить тип данных: кликните по иконке типа слева от заголовка столбца (ABC — текст, 123 — число, значок календаря — дата) и выберите нужный тип из списка.
• Изменить регистр: ПКМ по столбцу → «Преобразование» → «Нижний регистр», «Верхний регистр» или «С прописной буквы».
• Удалить пробелы: ПКМ → «Преобразование» → «Усечь».
• Удалить дубликаты: «Главная» → «Удалить строки» → «Удалить дубликаты».
• Добавить условный столбец: вкладка «Добавление столбца» → «Условный столбец» или «Настраиваемый столбец».
• Замена значений: «Главная» → «Замена значений» — меняет одно значение другим по всему столбцу.

Каждая из этих операций добавляет новый шаг в панель «Применённые шаги» и генерирует строку кода на языке M в строке формул.

Шаг 3 — Объединение таблиц

Power Query предлагает два способа объединения запросов:

Слияние по ключевому столбцу (аналог ВПР): «Главная» → «Объединить запросы» → «Объединить запросы в новый». Выберите ключевые столбцы в обоих запросах и тип соединения. Доступно 6 типов JOIN: внутреннее, левое внешнее, правое внешнее, полное внешнее, левое антисоединение и правое антисоединение. В отличие от ВПР, Power Query не замедляется на больших объёмах данных и не ограничен по строкам.

Добавление строк: «Главная» → «Добавить запросы» — объединяет строки из нескольких таблиц одинаковой структуры в один запрос.

Шаг 4 — Импорт данных в Excel

Завершив преобразования, нажмите «Закрыть и загрузить»: Power Query создаст смарт-таблицу на новом листе Excel. Кнопка «Закрыть и загрузить в…» открывает диалог с выбором формата:

• Таблица — смарт-таблица на новом или существующем листе.
• Отчёт сводной таблицы — данные сразу попадают в сводную таблицу.
• Сводная диаграмма — сводная диаграмма с данными из запроса.
• Только создать подключение — данные не выгружаются на лист; при включении флажка «Добавить в модель данных» запрос передаётся в Power Pivot.

Автоматическое обновление данных в Power Query

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

В этом и состоит главная ценность перед ручной обработкой: один раз настроил правила очистки и объединения таблиц — получаешь актуальный результат при каждом обновлении источника.

Язык M в Power Query: для продвинутых пользователей

Язык M (Power Query Formula Language) — функциональный язык запросов, на котором Power Query записывает каждое преобразование. При работе через интерфейс редактора код генерируется автоматически и отображается в строке формул. Для стандартных задач — фильтрации, смены типов, удаления дублей — вручную ничего писать не нужно. Сложные сценарии требуют прямой работы с кодом.

Что такое язык M и когда нужен вручную

Язык M регистрозависим: Table.SelectRows и table.selectrows — принципиально разные вещи. Базовая структура запроса — блок let … in с именованными шагами. Ключевое слово each определяет функцию одного аргумента; _ ссылается на текущую строку или значение.

Писать код вручную приходится в трёх случаях: вложенные условия (if … elseif … else), пользовательские функции для повторного использования логики и фильтрация строк по значениям из другой таблицы. В остальных ситуациях интерфейс редактора справляется без ручного кодирования.

Примеры формул на языке M: условия, Excel.Workbook и списки

Условный столбец — добавляет столбец «Статус» в зависимости от значения продаж:

Table.AddColumn(src, «Статус», each if [Продажи] > 10000 then «Высокие» elseif [Продажи] > 5000 then «Средние» else «Низкие»)

Подключение к книге Excel как источнику через функцию Excel.Workbook() — доступно в Excel 2013 и выше (в Excel 2010 эта функция не поддерживается):

Excel.Workbook(File.Contents(«путь\data.xlsx»), true)

Функция возвращает список листов и таблиц книги, из которых затем выбирают нужный через Навигатор.

Удаление строк с пустыми значениями:

Table.SelectRows(src, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {null})))

Язык M отвечает за подготовку данных — в отличие от DAX (языка Power Pivot), который используют для вычисления мер и агрегаций в модели данных.

Программа «Специалист по аналитике и базам данных в информационных системах» (256 часов, онлайн, с нуля) охватывает весь этот путь: от первого подключения к источнику до построения аналитических отчётов. Обучение бесплатно за счёт государственного финансирования; по итогам выдаётся официальный документ о квалификации. После учёбы доступна поддержка Центра карьеры — база 7 500+ актуальных вакансий, помощь с резюме и подготовка к собеседованиям.

Хотите освоить аналитику данных и Power Query без отрыва от текущей занятости и без вложений? В рамках федерального проекта «Активные меры содействия занятости» нацпроекта «Кадры» это реально. Смотрите каталог доступных программ.

Часто задаваемые вопросы

Что такое Power Query в Excel?

Power Query — бесплатная надстройка Microsoft Excel для автоматизации ETL-процессов: подключения к внешним источникам данных, их преобразования и загрузки в Excel или Power Pivot. Встроена начиная с Excel 2016 под именем Get & Transform. Работает без ограничений по количеству строк в отличие от стандартного листа Excel.

Как включить Power Query в Excel 2016?

Power Query в Excel 2016 встроен по умолчанию и называется Get & Transform. Перейдите: вкладка «Данные» → раздел «Скачать и преобразовать» → кнопка «Создать запрос». В Excel 2019, 2021 и Microsoft 365 путь: «Данные» → «Получить данные». Ничего дополнительно скачивать не нужно.

Как установить Power Query в Excel 2013?

Определите разрядность: Файл → Справка → «О программе» (32-bit или 64-bit). Скачайте бесплатный MSI-файл нужной разрядности с Microsoft Download Center. Запустите установщик и следуйте инструкции. После перезапуска Excel в ленте появится отдельная вкладка Power Query.

Где находится Power Query в Excel?

В Excel 2019, 2021 и Microsoft 365: вкладка «Данные» → «Получить и преобразовать данные» → «Получить данные». В Excel 2016: «Данные» → «Скачать и преобразовать» → «Создать запрос». В Excel 2010 и 2013: отдельная вкладка Power Query появляется после установки надстройки с Microsoft Download Center.

Чем Power Query отличается от Power Pivot?

Power Query — инструмент подготовки данных (ETL), язык — Язык M. Power Pivot — инструмент OLAP-анализа, язык — DAX. Используют совместно: Power Query очищает и загружает данные в модель данных ThisWorkbookDataModel, а Power Pivot строит поверх неё меры и отчёты.

Что такое язык M в Power Query?

Язык M (Power Query Formula Language) — функциональный язык запросов, на котором Power Query записывает каждый шаг преобразования. При работе через интерфейс код генерируется автоматически в строке формул. Для сложных задач — вложенные условия, пользовательские функции, Excel.Workbook() — формулы пишут вручную.

Сколько строк поддерживает Power Query?

Power Query работает без ограничений по строкам. Стандартный лист Excel ограничен 1 048 576 строками. При выгрузке данных в модель данных Power Pivot через Power Query листовое ограничение также не применяется.

Как объединить таблицы в Power Query?

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

Как научиться Power Query с нуля?

Освоение идёт поэтапно: сначала — загрузка источников и базовые преобразования через интерфейс редактора, затем — основы языка M для сложных условий. Программа «Специалист по аналитике и базам данных в информационных системах» (256 часов, с нуля) охватывает весь цикл работы с данными в Excel.

Можно ли пройти обучение Power Query бесплатно?

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

Обучение Power Query: с чего начать

Освоение Power Query идёт тремя ступенями: UI-операции в редакторе (фильтрация, смена типов, удаление дублей) → основы языка M (условные столбцы, пользовательские функции) → автоматизация регулярных отчётов и подключение к корпоративным базам данных.