Объединение нескольких таблиц в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

Объединение двух таблиц

Мы снова воспользуемся демонстрационной базой MS, относящейся к вымышленной компании по производству велосипедов AdventureWorks. Загрузите файлы Excel с примерами с сайта или из приложенного архива. (Если вы хотите установить базу AdventureWorks см. Дик Куслейка. Визуализация данных при помощи дашбордов и отчетов в Excel, раздел Базы данных SOL Server. Для выполнения примеров установка базы не требуется.) Мы объединим сведения о велосипедах и аксессуарах к ним:

Рис. 1. Цель – из двух таблиц Bikes и Accessories создать одну

Скачать заметку в формате Word или pdf, примеры в формате архива (внутри несколько файлов Excel без поддержки макросов)

Создайте новую книгу Excel. Нам понадобятся файлы C03E01 — Bikes.xlsx и C03E01 — Accessories.xlsx. Пройдите по меню Данные –> Получить данные –> Из файла –> Из книги. Выделите файл C03E01 — Bikes.xlsx, нажмите Импорт. В окне Навигатор выберите лист Bikes, нажмите стрелочку возле кнопки Загрузить, выберите Загрузить в… (Не требуется нажимать Преобразовать данные и входить в редактор Power Query, так как никаких преобразований с данными вы не планируете.) В окне Импорт данных установите Таблица, кликните Ok. Данные из файла C03E01 — Bikes.xlsx разместятся на листе новой книги Excel.

Если позже в файле C03E01 — Bikes.xlsx будут внесены изменения в существующие строки, или добавлены новые, для получения свежих данных достаточно кликнуть Данные –> Обновить все.

Повторите импорт для файла C03E01 — Accessories.xlsx до момента открытия окна Навигатор. Выберите лист Accessories, нажмите Преобразовать данные. В окне редактора Power Query, выберите запрос Accessories, и кликните Главная –> Добавить запросы –> Добавить запросы. В окне Добавление выберите таблицу Bikes, щелкните Ok.

Рис. 2. Окно Добавление

На панели Запросы (в левой части окна редактора PQ) находятся два запроса: Bikes и Accessories. После добавления запрос Accessories не соответствует своему исходному содержанию. Переименуйте его в Products. Загрузите запрос Products в таблицу на лист Excel. Выгрузите запрос Bikes с листа Excel. Для этого в Excel в области Запросы и подключения кликните на запросе Bikes правой кнопкой мыши и нажмите Загрузить в… установите Только создать подключение. Подтвердите сообщение системы о том, что данные будут удалены с листа. Удалите лишние листы Excel. Переименуйте лист с запросом Products в Products.

Стоит упомянуть, что такой подход не создал отдельного запроса Accessories. В будущем, при необходимости внести в запрос Accessories изменения, вы столкнетесь с трудностями при выборе корректного шага для редактирования. Лучше сначала создать два запроса Bikes и Accessories, установить для них Только создать подключение. А затем выбрав запрос Accessories в редакторе PQ пройти по меню Главная –> Добавить запросы –> Добавить запросы в новый.

Зависимости запроса

Только что мы объединили запросы в новый. Если запрос ссылается на другой запрос, то между ними устанавливается зависимость. Если запрос включает зависимости от других запросов, его нельзя удалить. Для просмотра зависимости между запросами в редакторе PQ пройдите Просмотр –> Зависимости запроса.

Рис. 3. Зависимости запроса

Ссылки

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

Создайте новую книгу Excel, и импортируйте таблицы Bikes и Accessories в режиме Только создать подключение. В редакторе PQ на панели Запросы кликните правой кнопкой мыши на запросе Accessories. Выберите Ссылка. Появится новый запрос Accessories (2). Переименуйте его в Products. Выделите запрос Products, выполните Главная –> Добавить запросы –> Добавить запросы. Добавьте запрос Bikes.

На языке М эти шаги выглядят так:

Рис. 4. Создание ссылки и добавление запроса на языке М

Чтобы увидеть этот код в редакторе PQ пройдите Просмотр –> Расширенный редактор.

Строка

соответствует команде Ссылка. Следующая строка – это операция добавления. Запросы перечисляются в фигурных скобках, которые служат для создания списка.

Представленный на рис. 4 код можно упростить: первый шаг удалить, а во втором явно прописать из каких таблиц формируется запрос Products:

Объединение трех таблиц

Создайте новую книгу Excel, и импортируйте таблицы Bikes, Accessories и Components в режиме Только создать подключение. В редакторе PQ на панели Запросы кликните правой кнопкой мыши на любом из запросов. Выберите Ссылка. Появится новый запрос, например, Accessories (2). Переименуйте его в Products. Выделите запрос Products, выполните Главная –> Добавить запросы –> Добавить запросы. Переключатель в позицию Три таблицы или больше.

Рис. 5. Добавление трех и более запросов

В окне Добавление новые запросы (но не исходный) можно упорядочить по вашему желанию. Также одну и ту же таблицу можно добавить несколько раз для дублирования данных в новой таблице. Нажмите Ok. Код М для этого шага примет вид:

Объединение четырех таблиц путем редактирования кода

Откройте файл Excel, созданный в предыдущем разделе. Импортируйте таблицу Clothing в режиме Только создать подключение. В редакторе PQ пройдите Просмотр –> Расширенный редактор. Отредактируйте строку кода. Было:

Стало:

Для проверки корректности формулы Table.Combine в Excel щелкните мышью на элементе управления фильтром в заголовке столбца ParentProductCategoryName. На панели Фильтр отобразятся четыре значения: Accessories, Bikes, Components и Clothing. Проверка значений с помощью фильтра служит удобным методом тестирования правильности шагов преобразований.