Power Query в Excel: обучение и основы¶

Power Query — это встроенная в Microsoft Excel технология для импорта, очистки, преобразования и объединения данных из различных источников, реализованная как подключаемый компонент «Получить и преобразовать» (Get & Transform). Инструмент позволяет автоматизировать рутинные операции обработки таблиц без написания формул и кода VBA, используя визуальный интерфейс и язык формул M. Обучение Power Query актуально для аналитиков, финансистов и всех пользователей Excel, работающих с большими объёмами неструктурированной информации.

¶История и доступность

Технология появилась как отдельное бесплатное дополнение Power Query для Excel 2010 и 2013, выпущенное Microsoft в 2013 году. В 2016 году компания интегрировала инструмент в основной интерфейс Excel, переименовав его в раздел «Данные» → «Получить и преобразовать». В Excel 2016, 2019, 2021 и Microsoft 365 Power Query доступен по умолчанию без дополнительной установки. В более ранних версиях (2010, 2013) требуется загрузка надстройки с сайта Microsoft. Аналогичная функциональность встроена в Power BI Desktop, где редактор Power Query является основным средством подготовки данных.

¶Основные возможности

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

¶Импорт данных

Инструмент поддерживает подключение к десяткам типов источников:

• файлы (Excel, CSV, TXT, XML, JSON, PDF);
• базы данных (SQL Server, Oracle, PostgreSQL, MySQL, Access);
• веб-страницы и веб-сервисы (API);
• облачные сервисы (SharePoint, Azure SQL, Dynamics 365);
• другие источники (Active Directory, Exchange, ODBC).

При импорте пользователь указывает путь, параметры подключения и предварительно просматривает данные в окне редактора.

¶Очистка и преобразование

Базовые операции включают: удаление строк и столбцов, изменение типов данных, замена значений, удаление дубликатов, фильтрация, сортировка, переименование заголовков. Продвинутые возможности позволяют разделять столбцы по разделителю, объединять столбцы, транспонировать таблицы, заполнять пустые ячейки предыдущими значениями, извлекать части текста (до/после разделителя, первые/последние символы), менять регистр, работать с датами и временем (извлечение года, месяца, дня недели, расчёт разницы).

¶Объединение и слияние

Power Query позволяет объединять данные из нескольких таблиц или файлов. Операция «Добавление запросов» выполняет вертикальную конкатенацию таблиц с одинаковой структурой. Операция «Объединение запросов» реализует SQL-подобные соединения (JOIN) по ключевым полям с выбором типа соединения: левое, правое, полное, внутреннее, анти-соединение. Также поддерживается объединение нескольких файлов из одной папки с автоматическим применением одинаковых преобразований ко всем файлам.

¶Язык M

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

¶Интерфейс редактора

Редактор Power Query открывается в отдельном окне и содержит следующие элементы:

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

Каждый шаг можно переименовать, удалить, изменить порядок или отредактировать. При удалении промежуточного шага последующие шаги пересчитываются, что требует осторожности при работе с зависимостями.

¶Процесс обучения

Обучение Power Query целесообразно начинать с освоения интерфейса и простых операций на учебных таблицах. Типичная последовательность изучения включает:

1. Подключение к файлу Excel или CSV и загрузка данных в редактор.
2. Практика базовых преобразований: удаление столбцов, фильтрация строк, замена значений.
3. Работа с типами данных и устранение ошибок форматирования (например, числа, сохранённые как текст).
4. Трансформация таблиц из «широкого» формата в «длинный» и обратно через команды «Перевернуть» и «Заполнить вниз».
5. Объединение таблиц по ключам и добавление запросов.
6. Создание условных столбцов и вычисляемых полей.
7. Изучение основ языка M для тонкой настройки запросов.
8. Автоматизация: настройка обновления запросов при открытии файла или по расписанию.

После освоения базовых приёмов переходят к реальным задачам: обработка выгрузок из 1С, CRM-систем, банковских выписок, объединение отчётов за разные периоды.

¶Практические примеры применения

• Обработка выгрузок из 1С: удаление шапок и служебных строк, приведение дат к единому формату, разделение номенклатуры на артикул и наименование.
• Консолидация файлов: сбор данных из десятков файлов Excel с одинаковой структурой из одной папки за пару кликов.
• Очистка справочников: удаление дубликатов контрагентов, приведение телефонов и ИНН к единому виду.
• Работа с веб-таблицами: импорт курсов валют, котировок или справочной информации с сайтов.
• Подготовка данных для сводных таблиц: нормализация «плоских» выгрузок в структуру, пригодную для анализа.

¶Преимущества и ограничения

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

¶Способы получения навыков

Обучение Power Query доступно через официальную документацию Microsoft Learn, видеоуроки на образовательных платформах, специализированные курсы по Excel и Power BI, а также тематические сообщества и форумы. Практические навыки закрепляются на реальных рабочих данных: рекомендуется начинать с небольших задач и постепенно усложнять сценарии обработки.