Power Query Базовый №7. Объединить по горизонтали (JOIN, Merge)

Файлы к уроку:

Ссылки:

Описание

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

В данном примере мы разберем объединение таблиц в Power Query. Это аналог операции LEFT JOIN из SQL. Для пользователей Excel данная операция тоже покажется знакомой. Больше всего она похожа на использование функции ВПР. Разница в том, что при использовании ВПР мы присоединяем данные только из одного столбца другой таблицы. Если же мы выполняем операцию объединения таблиц в Power Query, то объединяем таблицы полностью.

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

В таблице факта, например, может находиться детальная информация по продажам, а в таблице справочнике может находиться подробная информация о точке продаж: адрес, директор, тип точки и т. д.

Решение

В данном примере у нас есть 1 файла-источника. В первом находится детальная информация о продажах, а во втором справочная информация по торговым точкам. Нам нужно получить одну большую таблицу, т. е. к информации по продажам приклеить информацию по торговым точкам.

Чтобы объединить 2 таблицы сделаем следующее:

Подключимся к обеим таблицам
На вкладке Главная выберем команду Объединения
Укажем общий столбец

При объединении важно обратить внимание на то, чтобы общий столбец был с одним типом данных.

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

Csv.Document
Table.PromoteHeaders
Table.SelectColumns
Table.TransformColumnTypes
Excel.Workbook
Table.NestedJoin
JoinKind.LeftOuter
Table.ExpandTableColumn

Код

Код для подключения к первой таблице:

let
   source = Csv.Document(
      File.Contents(Путь & "И_ТЭКО.csv"),
      [Delimiter = ";", Columns = 16, Encoding = 65001, QuoteStyle = QuoteStyle.None]
   ),
   promote_headers = Table.PromoteHeaders(source, [PromoteAllScalars = true]),
   rows_select = Table.SelectColumns(
      promote_headers,
      {
         "Время",
         "Id инициатора",
         "Сумма в валюте получения",
         "Валюта получения",
         "Сумма в валюте внесения с комиссией",
         "Валюта внесения",
         "RUB (инициатор)"
      }
   ),
   types = Table.TransformColumnTypes(
      rows_select,
      {
         {"Время", type datetime},
         {"Сумма в валюте получения", type number},
         {"Сумма в валюте внесения с комиссией", type number},
         {"RUB (инициатор)", type number}
      }
   )
in
   types

Код для подключения ко второй таблице:

let
   source = Excel.Workbook(File.Contents(Путь & "И_СпрТочки.xlsx"), null, true),
   get_table = source{[Item = "Таблица1", Kind = "Table"]}[Data],
   types = Table.TransformColumnTypes(
      get_table,
      {
         {"ID POS", Int64.Type},
         {"Канал продаж", type text},
         {"ТМ", type text},
         {"Краткий адрес", type text},
         {"ID 2", type text}
      }
   )
in
   types

Код для объединения этих двух таблиц по горизонтали:

let
   source = Table.NestedJoin(
      table_1,
      {"Id инициатора"},
      table_2,
      {"ID 2"},
      "И_Точки",
      JoinKind.LeftOuter
   ),
   col_expand = Table.ExpandTableColumn(
      source,
      "И_Точки",
      {"ID POS", "Канал продаж", "ТМ", "Краткий адрес"},
      {"ID POS", "Канал продаж", "ТМ", "Краткий адрес"}
   )
in
   col_expand

Этот урок входит в Базовый курс 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	Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию.

предыдущая запись

Power Query Практический №14. Количество позиций в строке

следующая запись