Power Query для начинающих: что это и как автоматизировать отчёты в Excel и Power BI
Каждый понедельник вы открываете пять файлов от филиалов, копируете данные на один лист, удаляете пустые строки, исправляете даты, подтягиваете названия товаров через ВПР — и так каждую неделю. Power Query делает всё это автоматически: вы один раз показываете, что делать с данными, а дальше нажимаете «Обновить».
В этой статье объясним простыми словами, что такое Power Query, где его найти в Excel и Power BI, как работает редактор, и разберём на одном сквозном примере пять задач, которые он закрывает в работе аналитика. После этого станет понятно, почему Power Query — один из первых инструментов, которые изучают будущие аналитики данных.
Главное за 30 секунд
- Power Query — встроенный инструмент Excel и Power BI для получения, очистки и объединения данных.
- Каждое действие записывается как шаг запроса, поэтому обработка повторяется одним кликом «Обновить».
- Power Query не меняет исходные файлы — результат выводится отдельно.
- Код шагов пишется на языке M, но для старта достаточно кнопок.
Power Query, модель данных и DAX — на одном курсе
Курс Power BI в Prog Academy начинается с интерфейса Power Query: загрузка данных из Excel и Google Таблиц, очистка и трансформация, слияние запросов. Дальше — модель данных, функции DAX и готовый дашборд в портфолио за 2 месяца.
Программа курса Power BI →Что такое Power Query
Power Query — это инструмент для получения и подготовки данных, встроенный в Microsoft Excel и Power BI. В документации Microsoft он описан как движок преобразования и подготовки данных (data transformation and data preparation engine). Проще говоря, Power Query берёт данные откуда угодно, приводит их в порядок и передаёт в таблицу Excel или модель данных.
Ключевые возможности:
- Подключение к источникам: файлы Excel, CSV и TXT, целые папки с файлами, базы данных, веб-страницы, SharePoint, онлайн-сервисы.
- Преобразования: по данным Microsoft, более 350 типов трансформаций — от удаления дубликатов до разворота столбцов и группировки.
- Повторяемость: все действия записываются как шаги запроса. Когда появляются новые данные, достаточно обновить запрос.
- Безопасность: исходные данные не меняются, результат выводится отдельно.
По сути, Power Query — это ETL (extract, transform, load) для людей, которые не пишут код: получить, преобразовать, загрузить.
Где найти Power Query в Excel и Power BI
| Программа | Где искать | Примечания |
|---|---|---|
| Excel для Windows (Microsoft 365, 2024, 2021, 2019, 2016) | Вкладка «Данные» (Data) → группа получения и преобразования данных → Get Data | Встроен, отдельно устанавливать не нужно |
| Excel для Mac (Microsoft 365) | Вкладка «Данные» → Get Data | Обновление поддерживается для части источников: TXT, CSV, Excel, JSON, XML, SQL Server и некоторых других. Excel 2016 и 2019 для Mac Power Query не поддерживают |
| Excel для веба | Вкладка «Данные» | Доступна часть возможностей; Microsoft постепенно их расширяет |
| Power BI Desktop | Home → Get Data, Transform Data | Полный редактор Power Query, с него начинается любой отчёт |
| Excel 2010 и 2013 | Отдельная надстройка | Microsoft её больше не поддерживает — стоит обновить Excel |
Названия команд в статье приведены по англоязычному интерфейсу. В локализованных версиях Excel они переведены и могут немного отличаться в зависимости от версии, поэтому ориентируйтесь на расположение кнопок.
Как работает редактор: запрос и шаги
Когда вы подключаетесь к источнику и нажимаете Transform Data, открывается редактор Power Query. В нём четыре важные зоны:
- Лента с командами — вкладки Home, Transform, Add Column со всеми преобразованиями.
- Список запросов слева — каждый источник или промежуточная таблица является отдельным запросом.
- Предпросмотр данных в центре — как выглядит таблица после текущего шага.
- Applied Steps (применённые шаги) справа — история всех действий.
Applied Steps — сердце Power Query. Каждый клик («удалить столбец», «изменить тип», «отфильтровать») добавляет шаг. На любой шаг можно нажать и увидеть данные на этом этапе, удалить ошибочный шаг или изменить его настройки. Это похоже на запись макроса, но понятный и редактируемый.
Задача 1. Объединить все файлы из папки
Сквозной пример: сеть магазинов, каждый из трёх филиалов ежемесячно присылает файл sales_kyiv.xlsx, sales_lviv.xlsx, sales_odesa.xlsx с одинаковой структурой: товар (артикул) и продажи по месяцам в отдельных столбцах. Нужен один отчёт.
- Положите все файлы в одну папку.
- В Excel: Get Data → From File → From Folder, укажите папку.
- В окне со списком файлов нажмите Combine → Combine & Transform Data.
- Выберите лист или таблицу-образец. Power Query сам создаст вспомогательные запросы и сложит данные из всех файлов в одну таблицу, добавив столбец с названием файла.
Главное преимущество: в следующем месяце вы просто кладёте новые файлы в ту же папку и обновляете запрос. Копировать ничего не нужно.
Объединение из папки работает, когда во всех файлах одинаковые названия листов или таблиц и одинаковые заголовки столбцов. Если один филиал переименует столбец, в результате появятся пустые значения. Договоритесь с коллегами о шаблоне — или добавьте шаг, который переименовывает столбцы.
Задача 2. Почистить данные
Реальные данные почти всегда «грязные». Типичный набор шагов очистки в редакторе:
| Проблема | Команда | Где найти |
|---|---|---|
| Пустые строки внизу файлов | Remove Rows → Remove Blank Rows | Home |
| Пробелы в начале и в конце текста | Format → Trim | Transform |
| Даты и числа распознаны как текст | Data Type (тип данных столбца) | Home или Transform |
| Дубликаты | Remove Rows → Remove Duplicates | Home |
| Лишние столбцы | Choose Columns / Remove Columns | Home |
| Разное написание значений («Киев», «г. Киев») | Replace Values | Transform |
Совет: задавайте типы данных осознанно и как можно позже, после основных преобразований. Тип «дата» или «десятичное число» определяет, правильно ли потом будут считаться суммы и фильтры по периодам.
Задача 3. Развернуть месяцы из столбцов в строки (Unpivot)
В файлах филиалов продажи разложены по столбцам: «Январь», «Февраль», «Март»… Человеку так удобно читать, но для анализа и сводных таблиц нужен «длинный» формат: одна строка — один товар за один месяц.
| Было (широкий формат) | Стало (длинный формат) |
|---|---|
| Артикул | Январь | Февраль | Март | Артикул | Месяц | Продажи |
| A-101 | 120 | 95 | 140 | A-101 | Январь | 120 A-101 | Февраль | 95 A-101 | Март | 140 |
Как сделать: выделите столбцы, которые должны остаться (артикул, название файла), и выберите Transform → Unpivot Columns → Unpivot Other Columns. Переименуйте столбцы Attribute и Value в «Месяц» и «Продажи». Вариант «Unpivot Other Columns» лучше обычного: когда в файлах появится новый месяц, он автоматически попадёт в результат.
Задача 4. Сопоставить таблицы вместо ВПР (Merge)
В продажах есть только артикул, а название товара и категория — в отдельном справочнике. В Excel это обычно делают функцией ВПР (VLOOKUP) или XLOOKUP. В Power Query для этого есть Merge Queries:
- Загрузите справочник товаров как отдельный запрос.
- В запросе продаж выберите Home → Merge Queries.
- Укажите справочник и кликните ключевой столбец в обеих таблицах — артикул.
- Тип соединения — Left Outer: все строки продаж плюс соответствующие строки справочника.
- Разверните новый столбец и выберите нужные поля: название и категорию.
Merge работает быстрее тысяч формул ВПР и не ломается, когда меняется количество строк. Если же нужно не сопоставить, а дописать одну таблицу под другую (например, продажи за 2025 и 2026 годы), используйте Append Queries. Типы соединений здесь те же, что и в SQL: если вы знакомы с JOIN из статьи «SQL для начинающих», логика будет знакома.
Задача 5. Загрузить результат и обновлять одним кликом
- В редакторе нажмите Home → Close & Load (или Close & Load To, чтобы выбрать: таблица на листе, только подключение или модель данных).
- На основе таблицы постройте сводную таблицу или диаграмму.
- Когда появятся новые файлы, нажмите Data → Refresh All — Power Query повторит все шаги.
Для больших объёмов данных загружайте результат в модель данных, а не на лист: модель держит миллионы строк и позволяет строить связи между таблицами и вычисления на DAX.
Хотите не только чистить данные, но и анализировать их?
Power Query — один из первых шагов в профессии аналитика данных. На курсе Data Analytics в Prog Academy вы проходите весь путь: Excel, SQL, Power BI и Python, с практикой на реальных кейсах и портфолио. Начать можно с бесплатного вебинара «Как стать дата-аналитиком».
Язык M: что стоит знать новичку
Каждый шаг, который вы делаете мышкой, Power Query записывает на языке M (официальное название — Power Query Formula Language). Увидеть весь код запроса можно через Home → Advanced Editor. Вот как выглядит упрощённый запрос из задачи 3:
let
Source = Excel.Workbook(File.Contents("C:\Reports\sales_kyiv.xlsx"), true),
Sales = Source{[Item = "Sales", Kind = "Sheet"]}[Data],
Unpivoted = Table.UnpivotOtherColumns(Sales, {"Артикул"}, "Месяц", "Продажи"),
Typed = Table.TransformColumnTypes(Unpivoted, {{"Продажи", type number}})
in
Typed
Читать M просто: в блоке let каждая строка — это шаг с названием, и каждый следующий шаг берёт результат предыдущего. То, что стоит после in, — результат запроса. Новичку не нужно писать M с нуля: достаточно понимать структуру, чтобы поправить путь к файлу, название столбца или условие фильтра.
Отдельно об AI: Microsoft разворачивает в Excel для веба функцию Copilot, которая по текстовому описанию данных сама строит запрос Power Query, который затем можно редактировать и обновлять (по дорожной карте Microsoft 365, октябрь 2026 года). Это ускоряет старт, но проверять шаги всё равно придётся вам — поэтому понимание редактора и M никуда не исчезает.
Power Query, Power Pivot и Power BI: кто за что отвечает
| Инструмент | Отвечает за | Где живёт |
|---|---|---|
| Power Query | Получить, почистить и объединить данные | Excel, Power BI, онлайн-сервисы Microsoft |
| Power Pivot (модель данных) | Связать таблицы, хранить большие объёмы | Excel; в Power BI — модель отчёта |
| DAX | Вычисления и показатели на модели | Power Pivot, Power BI |
| Power BI | Интерактивные отчёты и дашборды, публикация для команды | Power BI Desktop и сервис |
Если сомневаетесь, хватит ли вам Excel или пора переходить на Power BI, — сравнение есть в статье «Power BI vs Excel: что лучше выбрать». Приятная новость: Power Query в обеих программах одинаковый, так что навыки переносятся без изменений.
Типичные ошибки
- Абсолютный путь к файлу на своём компьютере. Коллега открывает отчёт — и запрос не находит файлов. Храните источники в общей папке или на SharePoint.
- Смена типа данных на первом же шаге. Power Query часто добавляет шаг Changed Type автоматически; если позже появится новый столбец, этот шаг может сломаться. Проверяйте и при необходимости переносите его ниже.
- Unpivot Columns вместо Unpivot Other Columns. Новый месяц в данных не попадёт в отчёт.
- Inner join вместо Left Outer. Строки продаж без соответствующего артикула в справочнике тихо исчезают.
- Всё на листе. Сотни тысяч строк на листе тормозят Excel; загружайте в модель данных.
Частые вопросы
Что такое Power Query простыми словами?
Power Query — это инструмент для получения и подготовки данных, встроенный в Excel и Power BI. Он подключается к файлам, папкам, базам данных или веб-страницам, чистит и преобразует данные по записанным шагам, а затем обновляет результат одним кликом. Вы настраиваете обработку один раз, и дальше она повторяется автоматически.
Где найти Power Query в Excel?
В Excel для Microsoft 365, 2024, 2021, 2019 и 2016 для Windows Power Query встроен: это группа команд на вкладке «Данные» (Data) для получения и преобразования данных, а кнопка Get Data открывает список источников. Для Excel 2010 и 2013 существовала отдельная надстройка, но Microsoft её больше не поддерживает.
Меняет ли Power Query исходный файл?
Нет. Согласно документации Microsoft, Power Query не изменяет исходные данные: он читает их, применяет ваши шаги и выводит результат в отдельную таблицу или модель данных. Поэтому можно смело экспериментировать — оригинальный файл останется таким, каким был.
Работает ли Power Query на Mac и в Excel для веба?
Да, но с ограничениями. Excel для Mac в Microsoft 365 поддерживает Power Query и обновление из части источников — текстовых файлов, CSV, Excel, JSON, XML, SQL Server и некоторых других. Excel 2016 и 2019 для Mac его не поддерживают. В Excel для веба доступна часть возможностей, и Microsoft постепенно их расширяет.
Нужно ли учить язык M?
Для старта — нет. Каждое действие в редакторе автоматически записывается как строка кода на языке M, и большинство задач решается кнопками. Базовое чтение M понадобится, когда захотите поправить шаг, который нельзя настроить мышкой, или сделать запрос гибче. Начните с того, чтобы открывать Advanced Editor и смотреть, какой код создают ваши действия.
Чем Power Query отличается от Power Pivot и DAX?
Power Query отвечает за подготовку данных: получить, почистить, объединить. Power Pivot — это модель данных в Excel, где таблицы связываются между собой, а DAX — язык формул для вычислений в этой модели и в Power BI. Типичный порядок работы такой: Power Query готовит таблицы, модель их связывает, DAX считает показатели.
Сколько времени нужно, чтобы изучить Power Query?
Базовые операции — импорт, очистку, объединение таблиц и обновление — можно освоить за 1–2 недели регулярной практики на своих данных. Уверенная работа с языком M и сложными сценариями приходит за несколько месяцев. Быстрее всего учатся те, кто сразу переносит на Power Query свою реальную рутину с отчётами.
Источники
Об авторе
Термины из статьи
Короткие определения понятий, которые упоминаются выше.