Power Query Базовый №16. Виды объединения по горизонтали 2

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

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

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

• Антисоединение слева (ANTI JOIN)
• Соединение таблицы с ней же самой (SELF JOIN)

Антисоединение слева или ANTI JOIN используется, когда нужно при помощи правой таблицы отфильтровать левую. В результате вы получите левую таблицу, в которой не будет строк, которые совпали с правой таблицей.

Соединение таблицы с ней же самой или SELF JOIN используется, когда нам нужно добавить в таблицу столбцы с данными из других строк этой же таблицы. Примеры:

• Получить данные о продажах предыдущего дня для нахождения процентного изменения
• Получить данные о продажах в этом же месяце предыдущего года (same period last year)

Решение

Антисоединение

Антисоединение позволит нам исключить совпадающие значения правой таблицы из левой.

= Table.NestedJoin(Продажи, {"POSID"}, Фильтр, {"posid"}, "Фильтр", JoinKind.LeftAnti)

Процентное изменение

Чтобы вычислить процентное изменение к значению предыдущей строки нужно объединить таблицу с ней же самой, но со сдвигом на одну строку. Для этого используется два столбца индекса: один от 0, а второй от 1.

let
   source = Excel.CurrentWorkbook(){[Name = "Продажи"]}[Content], 
   types = Table.TransformColumnTypes(
      source, 
      {{"Дата", type date}, {"Продажи", Int64.Type}}
   ), 
   tab_add_index_1 = Table.AddIndexColumn(types, "Индекс 1", 1, 1), 
   tab_add_index_2 = Table.AddIndexColumn(
      tab_add_index_1, 
      "Индекс 2", 
      0, 
      1
   ), 
   tab_join = Table.NestedJoin(
      tab_add_index_2, 
      {"Индекс 2"}, 
      tab_add_index_2, 
      {"Индекс 1"}, 
      "Таблица", 
      JoinKind.LeftOuter
   ), 
   tab_add_col_1 = Table.AddColumn(
      tab_join, 
      "Продажи предыдущие", 
      each try [Таблица]{0}[Продажи] otherwise 0, 
      Number.Type
   ), 
   cols_select = Table.SelectColumns(
      tab_add_col_1, 
      {"Дата", "Продажи", "Продажи предыдущие"}
   )
in
   cols_select

Продажи год назад

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

let
   source = Excel.CurrentWorkbook(){[Name = "Продажи"]}[Content], 
   types = Table.TransformColumnTypes(
      source, 
      {{"Дата", type date}, {"Сумма продаж", Int64.Type}}
   ), 
   tab_add_col = Table.AddColumn(
      types, 
      "Дата год назад", 
      each Date.AddYears([Дата], - 1), 
      Date.Type
   ), 
   tab_join = Table.NestedJoin(
      tab_add_col, 
      {"Дата год назад"}, 
      tab_add_col, 
      {"Дата"}, 
      "Добавлен пользовательский объект", 
      JoinKind.LeftOuter
   ), 
   col_expand = Table.ExpandTableColumn(
      tab_join, 
      "Добавлен пользовательский объект", 
      {"Сумма продаж"}, 
      {"Сумма продаж год назад"}
   ), 
   tab_sort = Table.Sort(col_expand, {{"Дата", Order.Ascending}}), 
   cols_remove = Table.RemoveColumns(tab_sort, {"Дата год назад"})
in
   cols_remove

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

• Table.NestedJoin
• JoinKind.LeftAnti
• Table.TransformColumnTypes
• Int64.Type
• Table.AddIndexColumn
• JoinKind.LeftOuter
• Table.AddColumn
• Number.Type
• Table.SelectColumns

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