Power Query Практический №5. Продажи год назад (SELF JOIN)

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

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

• YouTube
• VK

Ссылки:

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

Описание

Наша задача — создать столбец, в котором напротив каждой даты будет находиться сумма продаж в этот же день/месяц, но ровно год назад.

Исходные данные выглядят так:

Добавим еще 1 столбец, в котором увидим сумму продаж год назад:

Для решения мы будем пользоваться следующим функционалом:

• Date.AddYears
• Группировка

Решение

Чтобы найти сумму продаж год назад нам нужно сначала найти дату год назад. Для этого мы воспользуемся функцией Date.AddYears.

После этого мы выполним операцию SELF JOIN, т. е. объединение таблицы по горизонтали с ней же самой.

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

• Table.AddColumn
• Date.AddYears
• Date.Type
• Table.NestedJoin
• JoinKind.LeftOuter
• Table.ExpandTableColumn
• Table.Sort
• Order.Ascending
• Table.TransformColumns
• Date.StartOfMonth
• List.Sum
• Date.Type

Код

let
   source = Продажи, 
   col_transform = Table.TransformColumns(
      source, 
      {{"Дата", Date.StartOfMonth, type date}}
   ), 
   tab_group = Table.Group(
      col_transform, 
      {"Дата"}, 
      {{"Сумма продаж", each List.Sum([Сумма продаж]), type number}}
   ), 
   tab_add_col = Table.AddColumn(
      tab_group, 
      "Дата_ГодНазад", 
      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}})
in
   tab_sort

Этот урок входит в Практический курс Power Query

Номер урока	Урок	Описание
1	Обработка типичной выписки	В этом уроке мы обработаем типичную банковскую выписку.
2	Интересная консолидация	В этом уроке мы будем практиковать объединение таблиц по вертикали.
3	Множественная консолидация при несовпадении заголовков	Объединим таблицы по вертикали, когда заголовки таблиц не совпадают, но порядок всегда одинаков.
4	Таблица дат	Для построения отчетов в модели данных Excel вам нужно создать таблицу дат. Сейчас вы узнаете как это сделать в Power Query.
5	Продажи год назад	В этом уроке мы узнаем как в Power Query получить таблицу с продажами прошлого года напротив текущих.
6	Консолидация книг и листов одновременно	В этом уроке мы научимся объединять все листы всех книг, т. е. выполним двухуровневую консолидацию.
7	Столбец общей суммы в PQ и PP	В этом уроке мы создадим столбец, в котором будет находиться общая сумма всех строк таблицы. Так же разберем эту же операцию в Power Pivot.
8	Скученные данные 3	В этом уроке мы обработаем еще один файл со скученными данными. На этот раз в одном столбце находятся даты и номенклатура. Нужно разбить этот столбец на два.
9	Строки преобразовать в столбцы	У нас есть таблица, в которой один столбец имеет скученные данные. Нам нужно скученный столбец преобразовать в столбцы.
10	Пивот, анпивот, группировка	В этом уроке мы попрактикуем анпивот, условную логику и группировку.
11	Нужные столбцы с нужного листа	В этом уроке мы научимся извлекать нужные столбцы с нужного листа не используя название самого листа.
12	Объединение, группировка, транспонирование, анпивот	В этом уроке на очень интересном примере из реальной жизни попрактикуем несколько техник Power Query: группировка, анпивот, объединение таблиц по горизонтали, транспонирование.
13	Нарастающий итог 4, много группировок	В этом уроке мы изучим еще 1 способ создать столбец нарастающего итога в Power Query. В отличие от других способов здесь мы не будем пользоваться формулами. Все сделаем при помощи пользовательского интерфейса.
14	Количество позиций в строке	В этом уроке мы посчитаем количество наименований, перечисленных в одной ячейке.
15	List Contains, Пользовательская функция	В этом уроке повторим создание пользовательских функций и рассмотрим еще 1 пример использования функции List.Contains.
16	ABC анализ	Научимся выполнять ABC анализ в Power Query.