Power Query — инструмент Excel для работы с данными

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

Он автоматизирует процессы ETL (Extract, Transform, Load) — извлечение, трансформацию и загрузку данных из различных источников. Это позволяет обойтись без ручной обработки данных и повторения однотипных действий.

Основные функции Power Query

У Power Query широкий набор возможностей для автоматизации работы с данными. Он позволяет:

• извлекать данные из множества различных источников, включая базы данных, облачные сервисы и файлы;
• очищать данные, удалять дубликаты и исправлять ошибки;
• преобразовывать данные с помощью фильтрации, объединения и выполнения над ними вычислений;
• автоматизировать процессы обработки новых и получения актуальных данных.

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

Если вы используете MS Excel 2016 и новее, то Power Query уже встроен в редактор и доступен через вкладку «Данные».

Для MS Excel 2010 и 2013 его нужно скачать и установить.

1. Определите тип своей системы: 32-разрядная (x86) или 64-разрядная (x64).
2. Скачайте соответствующий файл.

3. Запустите скачанный установщик MSI и следуйте инструкции.
4. После установки в Excel появится вкладка Power Query. Например, так это выглядит в MS Excel 2010.

5. Power Query готов к использованию.

Далее в примерах будем работать в MS Excel 2019.

Как подключиться к источникам данных

Допустим, у вас есть источник данных, которые вам нужно получить и обработать. 

1. Во вкладке «Данные» переходим в раздел «Получить и преобразовать данные».
2. Нажимаем на кнопку «Получить данные».
3. Здесь можно выбрать источник из основных категорий:

Файл: Excel, текстовые файлы, CSV, XML, JSON.

База данных: SQL Server, Access, Oracle, PostgreSQL.

Веб-службы: SharePoint, Dynamics 365, веб-API.

Azure (облачная платформа): база данных, хранилище таблиц.

Другие источники: OData, ODBC, веб-страницы.

Доступные типы данных 

Power Query поддерживает следующие форматы данных для извлечения:

Как работать в редакторе Power Query

Извлечение

Когда вы выбрали источник, данные из него нужно извлечь. Для примера возьмем другую таблицу Excel. Нажимаем:

• «Получить данные»
• «Из файла»
• «Из книги»

Откроется «Навигатор». Выберем таблицу, которую хотим загрузить. Когда файл Excel содержит несколько листов, то можно загрузить только выбранные.

Нажимаем на опцию загрузки «Загрузить в…». Откроется окно импорта данных.

В этом окне нужно выбрать:

— Способы представления данных

• Таблица

Стандартный способ отображения данных. Загружает данные в виде таблицы.

• Отчет сводной таблицы

Создает новую сводную таблицу на основе загруженных данных.

• Сводная диаграмма

Создает сводную диаграмму на основе загруженных данных.

• Только создать подключение

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

— Куда поместить данные

• Имеющийся лист

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

• Новый лист

Загружает данные в новый лист.

— Добавлять ли данные в модель данных

Если поставить галочку, то данные будут также загружены в модель данных Excel — ThisWorkbookDataModel. Это полезно, если вы хотите создать сводные таблицы или использовать DAX для анализа данных, а также объединять данные из различных источников.

После того как вы настроите загрузку и нажмете «ОК», загруженный элемент появится в панели «Запросы и подключения» во вкладке «Запросы». 

Во вкладке «Подключения» будет список всех активных подключений к источникам данных, которые Excel использует для извлечения информации. Например, модель ThisWorkbookDataModel будет находиться там.

После импорта данных их можно преобразовать с помощью редактора Power Query. Для этого нажмите на запрос двойным щелчком.

Откроется редактор.

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

1. Панель редактора с инструментами и командами в различных вкладках.
2. Список запросов в текущей рабочей книге.
3. Строка формул на языке M, о котором расскажем ниже.
4. Предварительный просмотр данных.
5. Свойства с именем запроса и дополнительными параметрами «Все свойства».
6. Примененные шаги или история преобразования.

Преобразование

• Переименование столбцов

Если в загруженной таблице названия столбцов некорректные, их можно изменить. 

В дальнейшем это поможет создавать сводные таблицы, объединять, связывать и сравнивать данные в столбцах с одинаковыми данными.

• Сортировка данных по возрастанию и убыванию

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

• Фильтрация

Обратите внимание, что в примере при загрузке данных из источника загрузились также и пустые строки. Они обозначены как null.

Чтобы отсортированные данные были корректными, нужно исключить (NULL) из фильтра.

Что касается языка M, то он используется для описания шагов. 

Например, когда мы применяем фильтрацию, то в строке отобразится такая функция:

= Table.SelectRows(#»Повышенные заголовки», each ([январь] <> null))

Где:

Table.SelectRows: функция, которая выбирает строки из таблицы на основе заданного условия. Она принимает два аргумента:

— первую часть (в данном случае таблицу, из которой будут выбраны строки);

— вторую часть (условие для выбора строк).

#»Повышенные заголовки»: ссылка на предыдущий шаг в запросе, который создает таблицу с повышенными заголовками (т. е. первая строка таблицы используется как заголовки колонок).

each ([январь] <> null): условие для фильтрации строк.

each — это специальное ключевое слово в языке M, которое позволяет применять условие к каждой строке таблицы.

([январь] <> null) — это логическое выражение, которое проверяет, что значение в колонке «январь» не является null (т. е. что ячейка не пуста).

• Замена данных

В ячейках также можно заменять данные. 

После нажатия на кнопку «Замена значений» откроется окно для ввода нового значения.

• Создание новых столбцов

Во вкладке «Добавление столбца» есть способы добавления столбцов.

Вы можете выбрать один из следующих вариантов:

«Столбец из примеров»: позволяет создать столбец, основанный на введенных вручную данных.

Например, если у вас есть столбец с полными именами и вы хотите создать новый столбец только с именами, вы можете ввести несколько примеров, например «Иван», «Мария», «Алексей». Power Query проанализирует ваши примеры и автоматически извлечет имена из всех полных имен в исходном столбце.

«Настраиваемый столбец»: позволяет создать новый столбец на основе формул.

Например, у вас есть таблица с двумя столбцами: «Цена» и «Количество». Вы хотите создать новый столбец «Сумма», который будет вычислять общую стоимость.

Формула для настраиваемого столбца может выглядеть так:

[Цена] * [Количество]

После создания столбца «Сумма» в каждой строке будет отображаться результат умножения цены на количество.

«Столбец индекса»: добавляет индексный столбец, который будет содержать последовательные номера для каждой строки.

Например, у вас есть таблица с данными о продажах, и вы хотите добавить индексный столбец для упрощения сортировки или ссылки на строки.

После добавления столбца индекса ваша таблица может выглядеть так:

Индекс помогает быстро идентифицировать каждую строку в таблице.

«Условный столбец»: добавляет новый столбец на основе заданного условия.

Например, у вас есть столбец «Баллы». Вы хотите создать новый столбец «Статус», который будет содержать значение «Прошел» для 60 баллов и выше и «Не прошел» для остальных баллов.

Условный столбец можно создать с помощью такого условия:

Если [Баллы] >= 60, то «Прошел», иначе «Не прошел»

• Остальные функции преобразования

Есть множество других функций преобразования данных. Вы можете транспонировать таблицу (поменять местами столбцы и строки), разделять столбцы, менять форматы (в том числе изменять регистр текста) и многое другое.

Объединение данных

В Power Query можно соединять и объединять таблицы из различных источников, чтобы создать единый набор данных.

1. Загрузим две таблицы через редактор Power Query.

Таблицы выглядят так:

2. Во вкладке «Главная» есть кнопка «Объединить запросы». Нажимаем «Объединить запросы в новый».

3. Откроется окно «Слияние». Выбираем таблицы, которые хотим объединить, и тип данных.

В выпадающем списке вы можете выбрать разные типы объединения:

— Внешнее соединение слева вернет все строки из первой таблицы и соответствующие данные из второй (если они есть).

— Внешнее соединение справа вернет все строки из второй таблицы и соответствующие данные из первой.

— Полное внешнее соединение вернет все строки из обеих таблиц, включая те, которые отсутствуют в одной из таблиц.

— Внутреннее соединение вернет только те строки, где значение присутствует в обеих таблицах.

— Антисоединение слева выберет все строки из первой таблицы, которые не имеют соответствующих данных во второй таблице.

— Антисоединение справа выберет все строки из второй таблицы, которые не имеют соответствующих данных в первой таблице.

4. В качестве примера выберем внутреннее соединение и выделим столбцы, по которым будем искать совпадения.

Получим результат слияния.

5. Нажмите «Закрыть и загрузить». Объединенная таблица появится на новом листе «Слияние1».

Автоматизация

Если данные в исходных файлах обновляются (например, новые строки добавляются в таблицы), проделанные шаги можно автоматически применить к новым данным.

После изменений в источнике данных достаточно просто нажать на кнопку «Обновить» на элементах в «Запросах и подключениях».

Если изменения произошли в таблице «Лист1», то обновляем ее и зависимую от нее таблицу — «Слияние1». 

Работа с типами данных

В Power Query у каждого столбца должен быть задан правильный тип данных для корректной работы с ним.

Во вкладке «Главная» редактора есть кнопка «Тип данных». Если тип указан неверный, то нужно выделить нужный столбец и применить правильный тип.

Как использовать язык M

Для расширения возможностей Power Query запросы можно составлять сразу на языке M, а не через инструменты интерфейса. Рассмотрим примеры.

• Создание сложных условных выражений

В интерфейсе Power Query есть базовые условные операторы, но сложные условия с несколькими уровнями вложенности или операциями нужно писать вручную.

Пример: сложное условие с несколькими проверками.

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

Эта строка добавляет новый столбец, где строки классифицируются как «Высокие», «Средние» или «Низкие» в зависимости от значения в столбце «Продажи». 

• Использование функций List и Record

Работа со списками и записями (List и Record).

Пример: удаление строк, содержащих только пустые значения.

Table.SelectRows(Источник, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {null})))

Запрос фильтрует строки, где все значения не пустые. 

• Динамическая генерация значений

Интерфейс Power Query не позволяет динамически генерировать списки или диапазоны значений. Для этого придется воспользоваться синтаксисом M.

Пример: создание последовательного списка чисел от 1 до 100.

List.Generate(() => 1, each _ <= 100, each _ + 1)

Создает список чисел от 1 до 100.

• Применение пользовательских функций

Хотя интерфейс позволяет использовать предопределенные функции, создание и вызов собственных функций требуют работы в редакторе M.

Пример: создание пользовательской функции для расчета НДС.

(цена) => цена * 0.2

Функция рассчитывает НДС на основе переданного значения. Пользовательские функции можно вызвать в формуле:

Table.AddColumn(Источник, «НДС», each myVAT([Цена]))

• Вложенные запросы и многократная фильтрация

Фильтрация с помощью вложенных запросов.

Пример: фильтрация на основе значений, рассчитанных в другой таблице.

Table.SelectRows(Источник, each [ID] = Table.First(Table.SelectRows(ДругаяТаблица, each [Имя] = «Алексей»))[ID])

Этот запрос находит строки, где ID совпадает с первой строкой другой таблицы.

• Работа с несколькими таблицами через вложенные запросы

Можно выполнять операции над несколькими таблицами одновременно.

Пример: обращение к другой таблице внутри запроса.

Table.SelectRows(Источник, each List.Contains(Table.Column(ДругаяТаблица, «ID»), [ID]))

Выбирает строки, где ID из одной таблицы встречаются в другой таблице.

• Уникальные и случайные значения

Генерация случайных чисел.

Пример: генерация 10 случайных значений.

List.Transform({1..10}, each Number.RoundDown(Number.RandomBetween(1, 100)))

Генерирует список из 10 случайных чисел между 1 и 100.

Подведем итог

• Power Query автоматизирует процессы ETL — извлечение, трансформацию и загрузку данных в Excel из других источников.
• В MS Excel 2016 и новее Power Query уже встроен, а в MS Excel 2010 и 2013 его придется установить как надстройку.
• Power Query поддерживает такие форматы файлов, которые можно представить в табличном виде.
• Преобразование файлов проводят в специальном редакторе, интерфейс которого состоит из панели инструментов, списка запросов, строки формул, предварительного просмотра, свойств и истории примененных шагов.
• Шаги отображаются в строке формул в виде запроса на языке M.
• Редактор позволяет сортировать данные, фильтровать их по нужному признаку, создавать новые столбцы в таблице из источника, объединять данные из таблиц.
• Когда данные в источниках меняются, их можно обновить в редакторе, чтобы применить изменения автоматически.
• Можно составлять запросы с помощью языка M. С его помощью создают сложные условия, работают с несколькими таблицами через вложенные запросы и многое другое.