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_%"