Что такое Power Query в Excel и как с ним работать?

Курс: AI Digital-маркетинг для менеджеров и предпринимателей

1 модуль

Управление цифровой воронкой

• Введение в интернет-маркетинг
• SCRUM в методологии AGILE
• Ключевые метрики CR, CPM, CPC, CPA, CPL, CPO
• Технологии для создания ИИ-агентов
• Установка и настройка облачной CRM

2 модуль

SEM — поисковый маркетинг и аналитика

• SEO — поисковая оптимизация сайта
• Создание и использование ИИ-агентов для SEO
• Оценка полученных результатов за предыдущий спринт
• Установка аналитики и целей в аналитике

3 модуль

SEM — поисковый маркетинг и аналитика. Часть 2

• Контекстная реклама
• Настройка ремаркетинга
• Использование ИИ-агентов для контекстной рекламы
• Сквозная аналитика
• Самостоятельная работа в проектных командах

4 модуль

Таргетированная реклама и работа с существующей аудиторией

• Оценка полученных результатов за предыдущий спринт
• SMM — маркетинг в социальных сетях
• Таргетированная реклама
• Разметка и анализ трафика
• Использование ИИ-агентов для креативов

Оценка

Предварительная оценка результатов предпринимательского digital-проекта и работа над ошибками

• Анализ полученных данных
• Разбор ошибок команд

Защита

Защита итогового проекта

• Презентация бизнес проекта
• Аттестация
• Получение удостоверения

Получить бесплатный урок

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

Кратко о главном

• Power Query — это встроенный ETL-инструмент Excel, который позволяет подключаться к внешним источникам, очищать и преобразовывать данные без написания формул или макросов.
• Все шаги обработки записываются в виде запроса и воспроизводятся автоматически при обновлении данных — это главное преимущество перед ручной работой с таблицами.
• Инструмент доступен в Excel 2016 и новее без дополнительной установки; в Excel 2010–2013 его можно подключить как бесплатную надстройку от Microsoft.
• Power Query не заменяет сводные таблицы или формулы — он работает на этапе подготовки данных, передавая чистый результат в лист Excel или модель данных Power Pivot.
• Эффект от внедрения зависит от регулярности обновления данных: разовая задача не требует Power Query, а еженедельная или ежедневная обработка одного и того же источника — прямой кандидат на автоматизацию.

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

Power Query — это инструмент для извлечения, преобразования и загрузки данных (ETL), встроенный в Microsoft Excel и Power BI. Его задача — взять «сырые» данные из любого источника, привести их к нужному виду и передать в рабочую книгу. Пользователь описывает последовательность шагов один раз, а затем повторяет её нажатием одной кнопки.

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

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

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

В Excel 2016, 2019, 2021 и Microsoft 365 Power Query расположен на вкладке «Данные» в группе «Получить и преобразовать данные». Кнопка «Получить данные» открывает меню со всеми доступными источниками. Редактор запросов запускается автоматически при выборе источника или через кнопку «Изменить» в панели запросов.

В Excel 2010 и 2013 Power Query устанавливается как отдельная надстройка. После установки появляется отдельная вкладка «Power Query» на ленте. Функциональность идентична более новым версиям, но интерфейс немного отличается расположением элементов.

• Excel 2016+, Microsoft 365: вкладка «Данные» → «Получить данные».
• Excel 2013: отдельная вкладка «Power Query» после установки надстройки.
• Excel 2010: надстройка доступна для 32- и 64-битных версий на сайте Microsoft.
• Power BI Desktop: Power Query встроен по умолчанию и открывается через «Преобразование данных».

Источники данных: что умеет подключать Power Query

Одна из ключевых сильных сторон инструмента — широкий список поддерживаемых источников. Power Query умеет подключаться к файлам, базам данных, веб\-сервисам и облачным хранилищам. Это позволяет собирать данные из разных систем в одном запросе без промежуточного экспорта.

Наиболее распространённые источники в корпоративной практике:

• Файлы: Excel (.xlsx, .xls), CSV, TXT, JSON, XML, PDF.
• Папки: автоматическое объединение всех файлов одного формата из указанной директории.
• Базы данных: SQL Server, MySQL, PostgreSQL, Oracle, Access.
• Веб: загрузка таблиц с HTML-страниц по URL.
• Облачные сервисы: SharePoint, OneDrive, Azure, Salesforce, Google Analytics (через коннекторы).
• OData и REST API: подключение к веб\-сервисам, возвращающим данные в структурированном формате.

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

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

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

Основные зоны редактора:

• Панель запросов (слева): список всех запросов в текущей книге. Запросы можно группировать, переименовывать и отключать от загрузки.
• Область предпросмотра (центр): показывает текущее состояние таблицы после применённых шагов. Данные отображаются в виде сетки.
• Панель «Параметры запроса» (справа): содержит имя запроса и список применённых шагов. Каждый шаг можно выбрать, чтобы увидеть промежуточный результат, или удалить, чтобы откатить изменение.
• Строка формул: показывает M-код текущего шага. Здесь можно редактировать логику вручную.
• Лента редактора: вкладки «Главная», «Преобразование», «Добавление столбца», «Просмотр» — основные инструменты трансформации.

Основные операции трансформации данных

Большинство задач по подготовке данных решается стандартными операциями редактора. Ниже — наиболее востребованные из них с объяснением, когда и зачем их применять.

Удаление лишних строк и столбцов

Исходные файлы часто содержат заголовки, итоговые строки, пустые строки или технические столбцы, которые не нужны в финальной таблице. Power Query позволяет удалить их один раз — и при каждом обновлении данных они будут исключаться автоматически. Операции доступны через меню «Главная» → «Удалить строки» или правый клик на заголовке столбца.

Изменение типов данных

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

Разделение и объединение столбцов

Столбец с полным именем можно разбить на «Имя» и «Фамилию» по разделителю (пробел, запятая). Обратная операция — объединение нескольких столбцов в один с заданным разделителем. Обе операции находятся на вкладке «Преобразование» → «Столбец текста».

Фильтрация строк

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

Группировка и агрегация

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

Объединение запросов (Merge и Append)

Merge (слияние) — аналог функции ВПР или JOIN в SQL: соединяет две таблицы по общему ключевому столбцу. Поддерживаются все типы объединений: левое, правое, внутреннее, полное внешнее. Append (добавление) — вертикальное объединение таблиц с одинаковой структурой, аналог UNION в SQL. Используется, например, для объединения ежемесячных отчётов в один годовой.

Разворачивание и сворачивание столбцов (Pivot и Unpivot)

Операция Unpivot преобразует «широкую» таблицу (где каждый месяц — отдельный столбец) в «длинную» (где все значения в одном столбце, а месяц — в другом). Это стандартный шаг при подготовке данных для сводных таблиц или визуализации. Pivot делает обратное — разворачивает строки в столбцы.

Пошаговый пример: объединение CSV-файлов из папки

Один из самых популярных сценариев — автоматическое объединение нескольких файлов одного формата. Например, каждый месяц в папку поступает новый CSV с данными о продажах, и нужно видеть сводную таблицу по всем периодам сразу.

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

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

Язык M: когда нужен код

Графический интерфейс покрывает большинство задач, но иногда требуется написать или отредактировать M-код напрямую. Это необходимо, например, для динамических параметров (путь к файлу зависит от текущей даты), условной логики (аналог IF с несколькими условиями) или пользовательских функций, которые применяются к каждой строке таблицы.

M-код каждого шага виден в строке формул. Полный код запроса доступен через «Расширенный редактор» (вкладка «Просмотр» → «Расширенный редактор»). Язык функциональный и чувствителен к регистру: Table.SelectRows и table.selectrows — разные вещи. Для большинства аналитиков достаточно уметь читать и незначительно редактировать автоматически сгенерированный код.

Типичные ошибки при работе с Power Query

Понимание распространённых проблем помогает избежать их при построении запросов и быстрее диагностировать неполадки при обновлении данных.

• Жёстко прописанные пути к файлам: если файл перемещается, запрос перестаёт работать. Решение — использовать параметры или относительные пути через SharePoint/OneDrive.
• Изменение структуры источника: если в исходном файле добавляется или переименовывается столбец, шаги, ссылающиеся на него по имени, выдают ошибку. Нужно обновить запрос вручную.
• Неверное определение типов данных: автоматическое определение типов зависит от первых строк данных. Если в первых строках число, а дальше текст — тип будет определён неверно. Рекомендуется всегда явно задавать типы.
• Загрузка всех запросов в листы: вспомогательные запросы (например, для разбора файлов из папки) не нужно загружать в лист. Их следует отключить через «Только создать подключение».
• Большие объёмы данных: Power Query обрабатывает данные в памяти. При работе с миллионами строк производительность может снижаться — в таких случаях стоит рассмотреть Power BI или прямые запросы к базе данных.

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

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

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

Когда Power Query действительно нужен

Инструмент оправдывает себя в конкретных сценариях. Если задача разовая и данные уже чистые — проще обойтись стандартными средствами Excel. Но в следующих ситуациях Power Query существенно экономит время:

• Регулярная обработка одного и того же источника (еженедельные отчёты, ежемесячные выгрузки из CRM).
• Объединение данных из нескольких файлов или систем в одну таблицу.
• Очистка «грязных» данных с нестандартными форматами, лишними пробелами, смешанными регистрами.
• Подготовка данных для сводных таблиц, когда исходная структура не подходит напрямую.
• Автоматизация отчётности без привлечения разработчиков или написания макросов на VBA.

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