Объединение нескольких таблиц в надстройке Power Query бывает двух видов:
1. Собрать данные из разных таблиц в одну (аналог — скопировать данные из разных файлов Excel и вставить таблицы одну под другой). Эта функция описана в статье:
Как в Power Query загрузить файл и объединить несколько файлов
Подтянуть данные из других таблиц по определенному критерию (аналог — функция ВПР или СУММЕСЛИ в Excel).
Объединение таблиц в Power Query (оба вида) находятся на вкладке Главная — блок Объединение.
В этой статье рассмотрим второй вид объединения — как в Power Query подтянуть данные из другой таблицы. Существует несколько видов объединения, рассмотрим самые популярные из них.
ВПР в Power Query или Внешнее соединение слева
Самый популярный способ сделать объединение таблиц в Power Query — это внешнее соединение слева. Оно является аналогом функции ВПР в Excel.
При внешнем соединении слева к значениям из основной таблицы подтягиваются значения из вспомогательной таблицы по ключевому столбцу.
Рассмотрим на примере, для которого уже сделаны два запроса к файлам Excel. В первом запросе находится таблица со списком сотрудников отдела Производство.
Во втором запросе — справочник должностей с указанием заработной платы для каждой должности.
Задача — подтянуть в первую таблицу со списком сотрудников данные по заработной плате из второй таблицы.
В первую очередь, определим ключевой столбец в каждой таблице.
Ключевой столбец, или ключ — это столбец в каждой из таблиц, в котором содержатся одинаковые по сути данные. В данном случае, в обеих таблицах этот столбец — Должность.
Обратите внимание: ключевые столбцы могут называться по-разному, но соответствующие данные в них должны иметь одинаковое написание. Регистр букв так же учитывается! Для Power Query слова “Бухгалтер” и “бухгалтер” — это два разных значения, и соответствия между ними нет (чтобы этого избежать, можно привести значения в одному регистру).
Чтобы сделать объединение таблиц в Power Query по ключевому столбцу, необходимо:
- Выделить первую таблицу (рабочую, в которую будем подтягивать данные)
- Перейти на вкладку Главная — Объединить запросы
3. В окне Слияние необходимо выбрать из выпадающего списка вторую таблицу, из которой будет подтягивать данные (первая таблица определилась автоматически, т.к. мы ее выделили на предыдущем шаге).
Выбираем таблицу Заработная плата.
Обратите внимание, что внизу окна Слияние указывается Тип соединения — в данном случае нам необходимо Внешнее соединение слева (все из первой таблицы, совпадающие из второй). Этот тип соединения устанавливается по умолчанию. Он означает, что в итоге останутся все значения из первой таблицы, и к ним подтянутся только соответствующие значения из второй.
Если для каких-то значений из первой таблицы не найдется соответствия во второй таблице — в этих ячейках будет значение NULL. Это не 0, а так называемая “пустота”.
4. Выделяем столбец-ключ в каждой таблице, нажав на заголовок (столбец Должность). Нажимаем Ок.
5. В таблице появился столбец Заработная плата, содержащий агрегированное значение Table.
6. Значение Table содержит в себе все значения, соответствующие ключу из данной строки, из таблицы Заработная плата. Развернем агрегированный столбец, чтобы получить нужный столбец из таблицы-источника Заработная плата.
Для этого нажмем на две стрелочки рядом с названием столбца Заработная плата. Затем нужно выбрать столбцы, которые необходимо вывести в таблицы — оставляем столбец Заработная плата, а со столбца Должность снимаем галочку, т.к. этот столбец уже есть в исходной таблице. Нажимаем Ок.
Обратите внимание, что на картинке сверху есть еще галочка Использовать исходное имя столбца как префикс. Если эта галочка установлена, то заголовок столбца будет состоять из названия таблицы и названия столбца.
Иногда это необходимо, если в запросе много объединений с разными таблицами (чтобы не запутаться в источниках), в противном случае галочку можно убрать.
В таблице появился новый столбец с заработными платами.
Но часть строк имеют значения null — для должности Инженер-технолог заработная плата на подтянулась. Для этого может быть 2 причины:
- в таблице-справочнике нет строки Инженер-технолог. Т.е. в принципе нет данных. Если бы для этой должности была указана заработная плата 0 — подтянулся бы 0, а не null.
- название должности Инженер-технолог в этих двух таблицах имеют разное написание (с маленькой и с большой буквы, например).
В нашем примере таблица-источник просто не содержит такой должности.
Внешнее соединение справа таблиц в Power Query
Внешнее объединение таблиц справа в Power Query оставляет все строки из второй таблицы, и подтягивает совпадающие значения из первой таблицы.
Этот вариант менее распространен на практике, однако, иногда он необходим. Сейчас увидим, для чего.
Выделим таблицу Производство и перейдем во вкладку Главная — Объединить запросы (аналогично предыдущему пункту). В окне Слияние выбираем таблицу для объединения (Заработная плата), выделяем ключевые столбцы в обеих таблицах (Должность), а затем в выпадающем списке Тип соединения выбираем Внешнее соединение справа (все из второй таблицы, совпадающие из первой). Нажимаем Ок.
Раскроем таблицу Заработная плата, как делали это в предыдущем пункте.
Как видим, появился столбец Должность.1, и в нем указаны все должности из таблицы Заработная плата. Но т.к. мы использовали для слияниz только должности отдела Производство, то строки из таблицы Заработная плата, которым не нашлось совпадений в таблице Производство, заполнились значением null.
При этом, напомню, что в первоначальном виде таблица Производство содержала сотрудников с должностью Инженер-технолог.
Но после того, как сделали объединение таблиц в Power Query, эти сотрудники исчезли. Почему?
Потому что во второй таблице Заработная плата не оказалось должности Инженер-технолог. А внешнее соединение справа берет все строки из второй таблицы (Заработная плата) и подтягивает только совпадающие строки из первой таблицы (Производство).
Для должности Инженер-технолог совпадения во второй таблице не нашлось, и эти строки пропали.
На практике внешнее соединение таблиц справа в Power Query часто используется для сверки двух таблиц.
Внешнее соединения слева и справа — это основные способы объединения таблиц в Power Query. Но есть и другие способы, т.к. они используются реже, рассмотрим их кратко на примерах.
Полное внешнее соединение (все строки из обеих таблиц)
Полное внешнее объединение таблиц в Power Query означает, что в итоговой таблице будут отображаться строки из обеих таблиц.
Делается оно полностью аналогично двум соединениям, описанным выше.
Результат полного внешнего соединения показан на картинке.
Строки, выделенные зеленым — значения ключевого столбца Должность есть в обеих таблицах, поэтому они полностью заполнены значениями из таблицы.
Желтые строки есть только в второй таблице, поэтому столбцы первой таблицы заполнены значениями null. Голубые строки есть только в первой таблице, поэтому значения из таблицы Заработная плата по ним не заполнено.
Внутреннее объединение таблиц в Power Query (только совпадающие строки)
При внутреннем соединении таблиц в Power Query в результате остаются только строки, которые есть в ключевом столбце в обеих таблицах.
В нашем примере получаем результат:
На практике внутреннее соединение таблиц в Power Query используется для фильтрации первой таблицы по значениям второй таблицы (отбирая только значения из второй таблицы, аналогично фильтру в Excel).
Анти-соединение слева
Этот вид объединения таблиц в Power Query оставляет в первой таблице только те строки, которые отсутствуют в ключевом столбце второй таблицы.
Как мы помним, должность Инженер-технолог отсутствует в таблице Заработная плата — именно поэтому строки с этой должность остались после объединения таблиц.
Анти-соединение справа
Этот вид объединения таблиц в Power Query оставляет только те строки из второй таблицы, которые отсутствуют в первой таблице.
В этот статье мы рассмотрели, как сделать объединение таблиц в Power Query разными способами.