Глава 9. Объединение таблиц и запросов Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Одна из классических проблем, которую решают профессионалам Excel – это объединение двух таблиц данных, и последующее создание сводной таблицы. Как правило, обработка основывается на функциях ВПР() или ИНДЕКС(ПОИСКПОЗ()). Power Query представил еще один относительно простой метод объединения двух таблиц. Предположим, вы хотите объединить две Таблицы, расположенные на листе Excel:

Ris. 9.1. Ishodnye Tablitsy na liste Excel

Рис. 9.1. Исходные Таблицы на листе Excel

Скачать заметку в формате Word или pdf, примеры в формате архива

Создание запроса-подключения

Чтобы объединить запросы, они должны существовать. Наличие Таблицы в Excel недостаточно; Power Query должен распознавать данные как запрос. Откройте файл Merge.xlsx. Чтобы загрузить таблицу Inventory в Power Query, щелкните любую ячейку в ней, пройдите по меню Данные –> Из таблицы/диапазона. Таблица будет импортирована в Power Query и отобразится в окне редактора. На вкладке Главная щелкните раскрывающийся список в нижней части кнопки Закрыть и загрузить. Выберите Закрыть и загрузить в… –> Только создать подключение.

Запрос отображается в области Запросы и подключения книги Excel, но Power Query не создал новую таблицу ни на текущем, ни на новом листе Excel:

Ris. 9.2. Novyj zapros sozdannyj tolko dlya podklyucheniya k Tablitse

Рис. 9.2. Новый запрос, созданный только для подключения к Таблице; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Если вы поспешили, и как обычно, кликнули Закрыть и загрузить, то на новом листе увидите новую Таблицу. Чтобы исправить ситуацию, щелкните правой кнопкой мыши запрос на панели Запросы и подключения. В редакторе Power Query пройдите по меню Главная –> стрелочка на кнопке Закрыть и загрузить –> Закрыть и загрузить в… –> Только создать подключение. Power Query предупредит, что вы собираетесь удалить данные. Подтвердите.

И наоборот, если вы создали запрос только для подключения, то в дальнейшем можете через этот интерфейс добавить Таблицу на лист Excel.

Теперь импортируйте в Power Query таблицы Sales. Аналогичным образом загрузите ее только создав подключение.

Объединение запросов

Данные –> Получить данные –> Объединить запросы –> Объединить. Откроется диалоговое окно Слияние. Выберите таблицу Sales в верхнем раскрывающемся списке, и таблицу Inventory – в нижнем. Однако, кнопка Ok по-прежнему не подсвечена:

Ris. 9.3. Vy vybrali tablitsy no pochemu vy ne mozhete prodolzhit

Рис. 9.3. Вы выбрали таблицы, но почему вы не можете продолжить?

Еще раз внимательно прочтите инструкцию, содержащуюся под заголовком окна. Power Query не знает, какие поля вы хотите использовать для выполнения слияния. Вам нужно в каждой таблице выделить столбцы с идентификатором. Причем так, чтобы в одной таблице этот столбец содержал уникальные значения, а в другой таблице значения могут повторяться. Такая связь называется «один ко многим», и ее использование – лучший способ гарантировать, что вы получите результаты, соответствующие вашим ожиданиям.

Power Query также поддерживает соединения один-к-одному и многие-ко-многим.

В нашем примере столбец SKU Number содержит уникальные продукты в таблице Inventory. Столбце SKU Number представлен и в таблице Sales. Здесь значения SKU Number могут повторяться много раз. Используйте этот столбец для связывания таблиц. Щелкните заголовок SKU Number в каждой таблице:

Ris. 9.4. Stolbtsy dlya svyazyvaniya vybrany korrektno

Рис. 9.4. Столбцы для связывания выбраны корректно

Обратите внимание, что предложенные вами столбцы для установления связи были проверены Power Query, и одобрены. Если, например, в таблице Sales вы выделите столбце Brand кнопка Ok, по-прежнему будет неактивна, а в нижней части окна Слияние появится сообщение об ошибке.

Нажмите кнопку Ok. Откроется редактор Power Query, в котором вторая таблица представлена одним столбцом:

Ris. 9.5. Novyj stolbets tablitsy Sales soderzhit sootvetstvuyushhie zapisi tablitsy Inventory

Рис. 9.5. Новый (последний) столбец таблицы Sales содержит соответствующие записи таблицы Inventory

Разверните столбец Inventory. Вопрос только в том, какие столбцы таблицы Inventory вам нужны. Итак, щелкните значок развернуть. Снимите галочку со столбцов, которые уже есть в таблице Sales (SKU Number and Brand), снимите флажок Использовать исходное имя столбца как префикс, нажмите Ok. Теперь сведения о продукте объединены с продажами:

Ris. 9.6. Detali iz tablitsy Inventory obedineny s dannymi tablitsy Sales

Рис. 9.6. Детали из таблицы Inventory объединены с данными таблицы Sales

Переименуйте запрос OneToMany. Главная –> Закрыть и загрузить. На листе Excel отобразится 20 строк Таблицы, как если бы свою работу выполнила функция ВПР:

Ris. 9.7. Sliyanie na osnove svyazi odin ko mnogim

Рис. 9.7. Слияние на основе связи один-ко-многим

Многие-ко-многим

При выполнении процедуры слияния нужно быть внимательным. Если вы попытаетесь выполнить слияние иным образом, вы обнаружите иной результат:

  • Данные –> Получить данные –> Объединить запросы –> Объединить.
  • Выберите Sales в верхней части, а Inventoryв нижней
  • Щелкните заголовок Brand в каждой таблице
  • Нажимать Ok
  • Щелкните значок развернуть
  • Снимите галочку со столбцов SKU Number and Brand
  • Снимите флажок Использовать исходное имя столбца как префикс
  • Нажмите Ok
  • Переименовать запрос ManyToMany
  • Главная –> Закрыть и загрузить

Новая процедура слияния отличается двумя аспектами: (1) таблицы Sales и Inventory в окне Слияние переставлены местами, (2) для связи выбран столбец Brand. Тем не менее, в Таблице на листе Excel появилось 22 записи – на 2 больше, чем исходное количество транзакций. Чтобы понять, почему это произошло, вернитесь в редактор Power Query, перейдите к первому шагу запроса ManyToMany (цифра 1 на рис. 9.8). Если вы перейдете к строке 19 и щелкните пробел справа от слова Table (2), вы увидите предварительный просмотр данных в таблице, которые при слиянии будут объединены в таблице Sales (3).

Ris. 9.8. Sliyanie mnogie ko mnogim v dejstvii

Рис. 9.8. Слияние многие-ко-многим в действии

В предыдущем слиянии вы связали данные на основе столбца SKU Number. В этом примере слияние осуществляется на основе столбца Brand. Однако бренду OK Springs соответствует два артикула в таблице Inventory. На основе этого примера вы можете увидеть, что нужно быть осторожным при создании слияний, чтобы не попасться в ловушку многие-ко-многим.


Прокомментировать