Power Query. Основы. Виды объединения таблиц по горизонтали

Все уроки Базового курса Power Query на этой странице или в этом плейлисте.

Описание

В этом уроке мы разберем основные виды объединения таблиц по горизонтали в Power Query. Вы узнаете, что такое левое, полное, внутреннее и кросс объединения таблиц по горизонтали.

Решение

Внутреннее объединение в Power Query

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

Левое объединение в Power Query

При левом объединении мы всегда получим все строки, которые есть в левой таблице. Из правой таблицы мы получим только совпадающие строки. В итоговой таблице мы получили две строки, заполненные полностью — это именно те строки, что мы получили при внутреннем объединении. Помимо этих двух строк в итоговой таблице присутствуют остальные строки из левой таблицы. В правой части итоговой таблицы для несовпадающих строк все ячейки заполнены значениями null.

Полное объединение в Power Query

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

Кросс объединение в Power Query

При кросс объединении каждая строка первой таблицы будет объединена со всем строками второй таблицы. При этом никаких совпадений даже не ищется.

Дублирование строк при объединении в Power Query

Поле, по которому мы объединяем таблицы будет объединено с каждой совпадающей строкой.

Примененные функции

• Excel.CurrentWorkbook
• Table.TransformColumnTypes
• Table.NestedJoin
• JoinKind.Inner
• JoinKind.LeftOuter
• JoinKind.FullOuter
• Table.ExpandTableColumn
• Table.AddColumn

Код

Код внутреннего соединения:

let
   inner_joined = Table.NestedJoin(
      Премьер,
      {"country"},
      Президент,
      {"country"},
      "presidents",
      JoinKind.Inner
   ),
   expanded = Table.ExpandTableColumn(
      inner_joined,
      "presidents",
      {"president"},
      {"president"}
   )
in
   expanded

Код левого соединения:

let
   left_joined = Table.NestedJoin(
      Президент,
      {"country"},
      Премьер,
      {"country"},
      "Премьер",
      JoinKind.LeftOuter
   ),
   expanded = Table.ExpandTableColumn(
      left_joined,
      "Премьер",
      {"prime_minister"},
      {"prime_minister"}
   )
in
   expanded

Код полного соединения:

let
   inner_joined = Table.NestedJoin(
      Президент,
      {"country"},
      Премьер,
      {"country"},
      "Премьер",
      JoinKind.FullOuter
   ),
   expanded = Table.ExpandTableColumn(
      inner_joined,
      "Премьер",
      {"country", "continent", "prime_minister"},
      {"country.1", "continent.1", "prime_minister"}
   )
in
   expanded

Код кросс-соединения:

let
   source = Ранг,
   cross_joined = Table.AddColumn(source, "Таблица", each Масть),
   expanded = Table.ExpandTableColumn(
      cross_joined,
      "Таблица",
      {"Масть"},
      {"Масть"}
   )
in
   expanded

Этот урок входит в Базовый курс Power Query

Номер урока	Урок	Описание
1	Зачем нужен Power Query. Обзор возможностей	Этот урок сам по себе является мини-курсом. Здесь вы узнаете для каких видов операций с данными создан Power Query.
2	Подключение Excel	Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов.
3	Подключение CSV/TXT, таблиц, диапазонов	Подключаемся к к файлам CSV/TXT, Excel.
4	Объединить таблицы по вертикали	Учимся объединять две таблицы по вертикали — combine.
5	Объединить по вертикали все таблицы одной книги друг за другом	Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel.
6	Объединить по вертикали все файлы в папке	Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке.
7	Объединение таблиц по горизонтали	Учимся объединять таблицы по горизонтали — JOIN, merge.
8	Объединить таблицы с агрегированием	Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY.
9	Анпивот (Unpivot)	Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными.
10	Многоуровневый анпивот (Анпивот с подкатегориями)	Более сложный вариант Анпивота — в строках находится несколько измерений.
11	Скученные данные	Данные собраны в одном столбце, нужно правильно его разбить на несколько.
12	Скученные данные 2	Разбираем еще один пример скученных данных.
13	Ссылка на другую строку	Как сослаться на другую строку.
14	Ссылка на другую строку 2	Как сослаться на другую строку, используя объединение по горизонтали.
15	Виды объединения таблиц по горизонтали	Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN.
16	Виды объединения таблиц по горизонтали 2	Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN.
17	Группировка	Изучаем операцию группировки с агрегированием — GROUP BY.
18	Консолидация множества таблиц пользовательской функцией	Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции.
19	Деление на справочник и факт	Разделим один датасет на два датасета: справочник и факт.
20	Создание параметра	Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query.
21	Таблица параметров	Создадим целую таблицу параметров и будем их использовать в запросах Power Query.
22	Объединение таблиц по вертикали, когда не совпадают заголовки столбцов	Как объединить две таблицы по вертикали, если названия столбцов не совпадают.
23	Поиск ключевых слов	Научимся искать ключевые слова в текстовом поле.
24	Поиск ключевых слов 2	Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию.