Как склеить таблицы в Power Query
Самое простое это переносом одна под другу.
Выбираем таблицы (они берутся из Power Query) и объединяем. Перед этим важно, чтобы кол-во и названия столбцов совпадало
Статья по теме
Чаще всего, чтобы объединить данные в Excel, пользователи копируют таблицы одну под другую. Или пишут формулы по типу ВПР, если в таблицу нужно добавить новые столбцы или аналитики. Но вы наверняка знаете, что самый удобный инструмент для объединения данных в Excel – это Power Query. Там есть два принципиальных способа:
- По вертикали – добавление таблицы под таблицу. Полезно, когда таблицы с одинаковой шапкой находятся в разных файлах или на разных листах.
- По горизонтали – слияние таблиц, похожее на ВПР. А здесь есть еще варианты — не только аналогичные ВПР, но и ВПР-наоборот, и ВПР-неВПР… Целых шесть видов объединения таблиц.
Разберем, чем эти способы отличаются друг от друга.
Вариант 1. Добавление таблицы под таблицу
Этот способ применяется, когда у вас есть несколько таблиц с одинаковой шапкой, но они находятся в разных местах, например на нескольких листах или в разных файлах. В таком случае, чтобы построить сводную таблицу, мы можем просто одну таблицу добавить под другую. Обращаю ваше внимание, что таблицы объединяются как-бы по вертикали.
Как на основе таблиц с одинаковой шапкой построить единую базу? Добавив таблицы одну под другую
Раньше процедуру добавления можно было выполнить только с помощью копирования. Отсюда – много ручной работы при внесении новых данных (ну, или писали макросы). Однако с момента, как в Excel появился Power Query, у нас появилась возможность добавлять таблицы одну под другую несколькими щелчками мыши:
Подробности про объединение данных с помощью Добавления смотрите в видео.
Вариант 2. Объединение таблиц
Объединение таблиц применяется, когда у вас есть две таблицы (шапки у них скорее всего разные), и одна таблица дополняет другую. Например, в отчете продажи показаны по городам, а у вас просят вывести информацию по областям. Тогда потребуется уже объединение по горизонтали:
Одна таблица дополняет другую с помощью объединения по общему столбцу
В «обычном» Excel это делается с помощью формулы ВПР или других формул с похожим функционалом. Power Query предлагает нам больше способов объединения, чем ВПР. Используя этот инструмент, мы можем не «просто» связать таблицы, но и сделать это по определенным условиям.
Подробности про Объединение данных смотрите в следующем видео.
Далее в статье подробно разбираются типы объединения данных. Чтобы вам было удобнее читать, приложены файлы с примерами.
Типы объединения данных в Power Query
Power Query предлагает на выбор шесть различных способов объединения таблиц:
- Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
- Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
- Полное внешнее (все строки из обеих таблиц)
- Внутреннее (только совпадающие строки)
- Анти-соединение слева (только строки в первой таблице)
- Анти-соединение справа (только строки во второй таблице)
Если посмотреть в интернете статьи про объединение данных, вы наверняка встретите поясняющие рисунки в виде кругов, где круги – это схематичное изображение множеств или таблиц, которые мы хотим объединить. Для Power Query иллюстрации по объединению данных выглядят точно так же:
Внешнее соединение слева
Внешнее соединение справа
Полное внешнее
Внутреннее соединение
Анти-соединение слева
Анти-соединение справа
Разберемся, что все это значит и как работают разные способы объединения.
Внешнее соединение слева
Left Outer Join
Внешнее соединение слева – самый часто встречающийся способ объединения таблиц. Это то, что мы делаем с помощью формулы ВПР в Excel – из одной таблицы «перетаскиваем» данные в другую.
Когда вы выбираете «Внешнее соединение слева», к данным из первой таблицы добавляются все значения из второй таблицы, соответствующие столбцу поиска. Если во второй таблице нет искомых значений, вы получите null (null – это значит пусто).
Пример: объединим план продаж в штуках с плановыми ценами.
Добавим таблицы в Power Query: вкладка Данные → Получить данные → Из других источников → Из таблицы / диапазона, или для новых версий Excel: вкладка Данные → Из таблицы / диапазона. Объединяем запросы: вкладка Главная → Объединить запросы (Объединить запросы в новый), выбираем столбец слияния «снаряд» и тип объединения «Внешнее соединение слева».
На некоторые товары плановые цены еще не установлены, поэтому после объединения в столбце «цена» для этих товаров будет проставлен null.
Хоть в диалоговом окне таблицы расположены одна под другой (просто так удобнее технически), связь все равно горизонтальная. В диалоговом окне вы указываете столбец, по которому выполняется объединение.
Обратите внимание, в нижней части окна появилось информационное сообщение: «Выбор согласовал 6 из первых строк (9)». Соответственно, в таблице с результатами цены заполнены только в шести строках, а в 3-х стоит null.
Внешнее соединение справа
Right Outer Join
Тип соединения «Внешнее соединение справа» используется, если в первую таблицу нужно не только добавить новые столбцы, но и отфильтровать её. Вторая таблица используется как источник данных и управляет содержимым первой таблицы.
Что происходит, когда вы выбираете «Внешнее соединение справа»: из первой таблицы исчезнут все не найденные во второй таблице данные. Из второй таблицы будут добавлены те значения, которых нет в первой. Если посмотреть на рисунки, то видно, что Внешнее соединение справа работает так же, как и Внешнее соединение слева – отличается только порядок расположения таблиц.
Пример: в первой таблице — ожидаемые среднемесячные продажи на одного покупателя, во второй – количество потенциальных покупателей.
Объединим таблицы, чтобы запланировать будущие продажи. Общий столбец для поиска — «город».
Смотрим, что получилось в результате объединения: из таблицы «исчезли» данные по городам, в которых нет потенциальных покупателей – Алматы и Санкт-Петербург. Зато появился город, в котором есть возможные покупатели – Астана. По этому городу потребуется проставить сумму ожидаемых средних продаж, чтобы выполнить дальнейшие расчеты.
Полное внешнее соединение
Full Outer Join
Этот тип соединения создает таблицу, в которой есть все строки и первой, и второй таблицы.
Что происходит, когда вы выбираете «Полное внешнее соединение»: с помощью заданного общего столбца будут объединены все строки из первой и второй таблиц.
Пример: в первой таблице приведен объем производства по дням, во второй – объем брака.
Для создания общей таблицы используется Полное внешнее соединение. Для объединения используем общий столбец «дата». В результате получится отчет, который с помощью некоторых доработок приводим к нужному виду:
Смотрим, что получилось: отчет с датами и цифрами из первой и второй таблицы. В ячейках, где данные за аналогичные периоды были только в первой или только во второй таблице, проставлено null.
Внутреннее соединение
Inner Join
Внутреннее соединение находит и оставляет только совпадающие строки из обеих таблиц. Если данные есть в одной из таблиц, но нет в другой, то в объединенном запросе они не появятся.
Что происходит, когда вы выбираете «Внутреннее соединение»: таблицы будут объединены с помощью заданного общего столбца. При этом из первой таблицы исчезнут все не найденные в «общем» столбце второй таблицы строки. Из второй таблицы – исчезнут не найденные в первой. В математике эта операция называется «пересечением множеств».
Пример: есть два списка покупателей, принявших участие в акциях А и Б – по одному для каждой из акций. С помощью Внутреннего соединения получим список покупателей, участвовавших и в той, и в другой акции.
Поиск выполняем по общему столбцу «Покупатель». Результат — список покупателей, участвовавших в обеих акциях:
Анти-соединение слева
Left Anti Join
Если вам требуется исключить из таблицы ненужные строки, можно воспользоваться объединением с Анти-соединением слева. С помощью этого способа из первой таблицы будут исключены все строки, найденные во второй.
Пример: в первой таблице – записи о сделанных покупателям коммерческих предложениях, во второй – данные о продажах. С помощью Анти-соединения слева найдем покупателей, которые получили предложение, но не сделали покупку.
С помощью запроса исключим из первой таблицы найденных во второй покупателей:
В результате получили список покупателей, которые не сделали покупки.
Анти-соединение справа
Right Anti Join
Анти-соединение справа работает так же, как и Анти-соединение слева. Разница здесь только в порядке расположения таблиц.
Пример: в первой таблице – отчет о выполненных задачах, во второй – список поставленных задач. Объединив таблицы с помощью Анти-соединения справа, найдем невыполненные задачи.
В результате объединения получим список невыполненных задач:
Кроме объединения таблиц с помощью запросов Power Query, такие операции можно выполнять и с помощью DAX-формул, применяя их в различных сочетаниях: GENERATEALL, NATURALLEFTOUTERJOIN, NATURALINNERJOIN, CROSSJOIN, FILTER+CROSSJOIN, GENERATE, EXCEPT и др.