Power Query Базовый №17. Группировка

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

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

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

Описание

В этом уроке мы научимся выполнять группировку в Power Query. Группировка — это операция, при которой вы находите уникальные значения в одном поле и выполняете агрегирования каких-то других полей. Например, в этом уроке мы найдем сумму продаж на каждый день, а еще посчитаем количество сделок на каждый день, найдем максимальную сделку на каждый день и выполним другие агрегирования.

Группировку можно также выполнять и по нескольким полям. В таком случае мы получим агрегаты для каждого уникального сочетания этих нескольких полей.

Группировка в Power Query — это аналог операции GROUP BY из SQL. Так же это напоминает функции СУММЕСЛИ/СУММЕСЛИМН, СЧЕТЕСЛИ/СЧЕТЕСЛИМН, СРЗНАЧЕСЛИ и другие подобные функции. Если вы искали аналоги этих функций в Power Query, то вам нужно воспользоваться группировкой.

Исходная таблица:

Итоговая таблица:

Решение

Задачу можно решить используя только лишь пользовательский интерфейс Power Query.

Нужно выбрать столбец, по которому мы будем делать группировку, перейти на вкладку Главная — Группировать по.

Далее нужно выбрать столбцы для агрегирования и вид агрегирования.

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

• Table.RemoveColumns
• Table.Skip
• Table.PromoteHeaders
• Table.SelectRows
• Table.TransformColumns
• Text.Start
• Text.From
• Table.TransformColumnTypes
• Int64.Type
• Table.Group
• List.Sum
• Table.Sort
• Order.Ascending
• Table.AddColumn

Код

let
   source = Excel.Workbook(File.Contents(Путь), null, true), 
   get_table = source{[Name = "Источник"]}[Data], 
   cols_select = Table.RemoveColumns(get_table, {"Column1"}), 
   rows_skip = Table.Skip(cols_select, 10), 
   tab_promote_headers = Table.PromoteHeaders(
      rows_skip, 
      [PromoteAllScalars = true]
   ), 
   rows_skip_2 = Table.Skip(tab_promote_headers, 1), 
   rows_select = Table.SelectRows(rows_skip_2, each ([Время] <> null)), 
   col_text_start = Table.TransformColumns(
      rows_select, 
      {{"Время", each Text.Start(Text.From(_, "ru-RU"), 10), type text}}
   ), 
   types = Table.TransformColumnTypes(
      col_text_start, 
      {
         {"Время", type date}, 
         {"Кол-во", Int64.Type}, 
         {"Цена", type number}, 
         {"Себест.", type number}, 
         {"Сумма", type number}, 
         {"Сумма себест.", type number}
      }
   ), 
   tab_group = Table.Group(
      types, 
      {"Время"}, 
      {
         {"Количество", each List.Sum([#"Кол-во"]), type number}, 
         {"Себестоимость", each List.Sum([#"Сумма себест."]), type number}, 
         {"Сумма", each List.Sum([Сумма]), type number}
      }
   ), 
   tab_sort = Table.Sort(tab_group, {{"Время", Order.Ascending}}), 
   tab_add_col_profit = Table.AddColumn(
      tab_sort, 
      "Прибыль", 
      each [Сумма] - [Себестоимость], 
      type number
   ), 
   value_sum = List.Sum(tab_add_col_profit[Прибыль]), 
   tab_link = tab_add_col_profit, 
   #"tab_add_col_%" = Table.AddColumn(
      tab_link, 
      "Доля от общей прибыли", 
      each [Прибыль] / value_sum, 
      Percentage.Type
   )
in
   #"tab_add_col_%"

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