Как в power query объединить таблицы

Слияние запросов (Power Query)

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

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

Сведения о слиянии запросов

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

Существует два типа операций слияния:

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

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

Представление в диалоговом окне

Выполнение слияния

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

Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос >Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
Выберите Главная >Запросы слияния. По умолчанию выполняется встроенное слияние. Чтобы выполнить промежуточное слияние, щелкните стрелку рядом с командой, а затем выберите Объединить запросы как новые.

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

Диалоговое окно

Левое внешнее соединение Сохраняет все строки из первичной таблицы и возвращает все совпадающие строки из связанной таблицы.

Правое внешнее соединение Сохраняет все строки из связанной таблицы и возвращает все совпадающие строки из основной таблицы.

Полный внешний Возвращает все строки из основной и связанной таблиц.

Левое анти-соединение Возвращает только строки из основной таблицы, в которых нет совпадающих строк из связанной таблицы.

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

Result (Результат)

Завершение слияния

Разверните столбец Таблица

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

Развернуть

Развернуть

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

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

Выберите таблицу «Категории», а затем выберите «Данные»>«&» > «Из таблицы» или «Диапазон».
Выберите «Закрыть& Загрузить таблицу, чтобы вернуться на лист, а затем переименуем ярлыж листа в «Категории PQ».
Выберите таблицу «Данные о продажах», откройте Power Query, а затем на домашней>в>объединить запросы >слияние как новые.
В диалоговом окне «Слияние» под таблицей «Продажи» выберите в списке столбец «Название товара».
В столбце «Название товара» выберите таблицу «Категория» из списка.
Чтобы завершить операцию, выберите «ОК».

6 видов Table.NestedJoin в Power Query: Объединяем таблицы в Excel и Power BI

Самый удобный инструмент для объединения таблиц – надстройка Power Query. Сегодня разберем 6 базовых типов объединения таблиц в Power Query. Пробежимся по особенностям соединений, найдем плюсы и минусы разных способов и выберем оптимальные.

Power Query предлагает на выбор шесть различных способов объединения таблиц:

Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
Полное внешнее (все строки из обеих таблиц)
Внутреннее (только совпадающие строки)
Анти-соединение слева (только строки в первой таблице)
Анти-соединение справа (только строки во второй таблице)

Для разных целей вам понадобятся разные виды соединений. Несмотря на то, что способы 1 и 2, а также 5 и 6 — зеркальны, мы разберём каждый из них.

Внешнее соединение слева (Left Outer Join)

Из правой таблицы «перетаскиваем» данные в левую (в Excel это делают обычно с помощью ВПР). К данным из первой таблицы добавляются все значения из второй таблицы, соответствующие столбцу поиска. Если во второй таблице нет искомых значений, вы получите значение null (null – это значит пусто).

Пример: объединим продажи товаров с ценами.

Шаг 1. Добавим таблицы в Power Query.

Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы / диапазона. Для новых версий Excel: вкладка Данные → Из таблицы / диапазона.

Шаг 2. Создадим подключения

После загрузки вернемся обратно в Excel из Power Query командой «Закрыть и загрузить — Закрыть и загрузить в. «. В появившемся затем окне выбираем «Только создать подключение».

Повторяем то же самое со второй таблицей с ценами. Теперь в Power Query появились таблицы, которые можно объединять.

Шаг 3. Объединим таблицы

Создадим третий запрос, который будет объединять и сравнивать данные из предыдущих двух запросов.

Для этого выберем в Excel на вкладке Данные — Получить данные — Объединить запросы — Объединить или нажмем кнопку Объединить на вкладке Power Query.

В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим тип соединения «Внешнее соединение слева»:

Далее появится таблица из трех столбцов. Третий столбец содержит таблицы цен по каждому товару — эти таблицы нужно развернуть с помощью двойной стрелки в шапке:

Оставляем галочки для нужных столбцов и нажимаем ОК. Можно не использовать имя столбца как префикс — это только загружает заголовки.

В итоге получим слияние данных из обеих таблиц. На некоторые товары цены еще не установлены, поэтому в столбце «цена» для этих товаров будет значение null:

Осталось выгрузить получившийся отчет на лист Excel с помощью кнопки «Закрыть и загрузить» на вкладке Главная. Объединение выполнено успешно!

Этот тип Join довольно удобен если у вас есть таблица статистики, в которую нужно подтянуть дополнительные столбцы.

Внешнее соединение справа (Right Outer Join)

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

«Внешнее соединение справа» работает так же, как и «Внешнее соединение слева» – отличается только порядок расположения таблиц. В связи с этим оно редко используется на практике.

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

Объединим таблицы, чтобы рассчитать будущие выплаты сотрудникам. Общий столбец для поиска — «сотрудники».

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

Полное внешнее соединение (Full Outer Join)

В этом случае будут объединены все строки из первой и второй таблиц. Это самый безопасный способ соединения двух таблиц — вы не теряете данные. Такой способ часто помогает сравнить данные двух таблиц и найти расхождения.

Пример: объединим таблицы по объему производства и количеству брака.

В данном примере попробуем объединить таблицы по нескольким столбцам. Для создания общей таблицы используется «Полное внешнее соединение». Для объединения используем общие столбцы «Дата» и «Подразделение» — выбираем их в нужном порядке в каждой таблице, зажимая клавишу ctrl:

В результате получится отчет, который нужно немного доработать:

Приводим к нужному виду нашу объединенную таблицу:

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

Внутреннее соединение (Inner Join)

При таком типе соединения в общей таблице остаются только совпадающие строки из обеих таблиц. Если вас интересуют только пересечения таблиц, Inner Join — самый подходящий способ.

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

Для решения этой задачи также объединим таблицы по нескольким столбцам. Для создания общей таблицы используется тип соединения «Внутреннее (только совпадающие строки)».

Получим список сотрудников, участвующих в обоих тестах:

После фильтрации и удаления лишних столбцов приводим объединенную таблицу к нужному виду:

Анти-соединение слева (Left Anti Join)

При Left Anti Join подтянутся все данные из левой таблицы, которых нет в правой таблице. Можно сказать, анти-ВПР.

Если надо вычесть одну таблицу из другой — вам подойдёт именно Left Anti Join.

Пример: в первой таблице – список клиентов и количество звонков, во второй – данные о сделках. Нужно найти клиентов, которым звонили, но они ничего не купили.

Исключаем из первой таблицы всех клиентов второй таблицы. Для создания общей таблицы используется «Анти-соединение слева»:

После удаления лишнего столбца получаем готовую таблицу клиентов, которые ничего не купили:

Анти-соединение справа (Right Anti Join)

С помощью Right Anti Join из второй таблицы будут исключены все строки, найденные в первой таблице. По сути это зеркальное отражение Left Anti Join, поэтому используется редко.

Пример: в первой таблице — участники акций, во второй – клиенты и суммы оплат. Следует найти клиентов, которые совершили сделки, но не участвовали в акциях.

Нужно исключить из второй таблицы всех найденных клиентов в первой таблице. Для создания общей таблицы используется «Анти-соединение справа».

Power Query на этапе слияния всегда делает нам подсказку: «Выделенный фрагмент исключает строки из второй таблицы (4 из 6)». В результате получаем запрос такого вида:

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

Заключение

Мы рассмотрели 6 базовых соединений таблиц в Power Query. 4 из них довольно часто применяются на практике, а 2 — являются зеркальными и используются редко.

Для подтягивания дополнительных данных к таблицам статистики рекомендуем использовать Left Join. Если вы хотите сравнить несколько таблиц между собой, вам пригодится Full Outer Join. Когда нужно найти пересечения таблиц — пользуйтесь Inner Join. Ну а для вычитания одной таблицы из другой всегда можно задействовать Left Anti Join.

«Right Join» и «Right Anti Join» на практике обычно не используются, так как являются зеркальным отражением «Left Join» и «Left Anti Join». Но на всякий случай мы включили их в описание, чтобы вы увидели как их использовать.