Power Query. Формулы М. Группировка, Пивот, Анпивот, Сортировка

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

    Описание

    В этом уроке мы изучим функции, которые позволят нам выполнять операции Группировки, Пивот, Анпивот и Сортировки.

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

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

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

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

    Сортировка — это настройка порядка столбцов. Столбцы можно упорядочить по возрастанию или убыванию.

    В этом уроке мы изучим/повторим:

    Посчитать количество строк в таблице с Table.RowCount

    Посчитать количество строк в таблице с Table.Group

    Подробный разбор функции Table.Group

    Параметр GroupKind.Global и GroupKind.Local

    Сортировка с функцией Table.Sort

    Параметры Order.Ascending и Order.Descending

    Фильтрация с помощью Table.SelectRows

    Операция Анпивот с функцией Table.Unpivot и Table.UnpivotOtherColumns

    Операция Пивот с функцией Table.Pivot

    Посчитать количество строк

    Чтобы посчитать количество строк можно воспользоваться функцией Table.RowCount.

    let

       /* Открываем таблицу */

       source = Excel.CurrentWorkbook(){[Name = "Таблица1"]}[Content],

       /* Изменение типа данных */

       cos_types = Table.TransformColumnTypes(

          source,

          {

             {"Квартал", type text},

             {"Месяц", type text},

             {"Прибыль", Int64.Type}

          }

       ),

       value_counts = Table.RowCount(cos_types)

    in

       value_counts

    Посчитать количество строк с помощью Table.Group:

    let

       /* Открываем таблицу */

       source = Excel.CurrentWorkbook(){[Name = "Таблица1"]}[Content],

       /* Изменение типа данных */

       cols_types = Table.TransformColumnTypes(

          source,

          {

             {"Квартал", type text},

             {"Месяц", type text},

             {"Прибыль", Int64.Type}

          }

       ),

       value_row_count = Table.Group(

          cols_types,

          {},

          {{"Счет строк", each Table.RowCount(cols_types), type number}}

       )

    in

       value_row_count

    Выполнить группировку

    Выполним группировку с агрегированием. Получим из левой таблицы правую.

 

    

    let

       /* Загрузка источника */

       source = Excel.CurrentWorkbook(){[Name = "Таблица1"]}[Content],

       /* Типы данных */

       cols_types = Table.TransformColumnTypes(

          source,

          {

             {"Квартал", type text},

             {"Месяц", type text},

             {"Прибыль", Int64.Type}

          }

       ),

       tab_group = Table.Group(

          cols_types,

          "Квартал",

          {

             {"Сумма прибыли", each List.Sum([Прибыль]), type number},

             {"Количество", each Table.RowCount(_), type number}

          }

       )

    in

       tab_group

    Группировка с параметром GroupKind.Local

    Мы хотим из левой таблицы получить правую. Группировка будет

 

 

    let

       source = Excel.CurrentWorkbook(){[Name = "Таблица2"]}[Content],

       cols_types = Table.TransformColumnTypes(

          source,

          {

             {"Дата", type datetime},

             {"Сотрудник", type text},

             {"Сумма сделки", Int64.Type},

             {"Количество", Int64.Type}

          }

       ),

       tab_group = Table.Group(

          cols_types,

          {"Дата", "Сотрудник"},

          {{"Сумма", each List.Sum([Сумма сделки]), type number}},

          GroupKind.Local

       )

    in

       tab_group

    Сортировка таблицы

    Сортировка таблицы — это настройка порядка строк. В данном примере мы сортируем таблицу в следующем порядке:

    Дата — по возрастанию

    Сотрудник — по возрастанию

    Количество — по убыванию

    let

       source = Excel.CurrentWorkbook(){[Name = "Таблица2"]}[Content],

       cols_types = Table.TransformColumnTypes(

          source,

          {

             {"Дата", type datetime},

             {"Сотрудник", type text},

             {"Сумма сделки", Int64.Type},

             {"Количество", Int64.Type}

          }

       ),

       tab_sort = Table.Sort(

          cols_types,

          {

             {"Дата", Order.Ascending},

             {"Сотрудник", Order.Ascending},

             {"Количество", Order.Descending}

          }

       )

    in

       tab_sort

    Фильтрация

    Фильтрация — это отбор строк. В данном примере мы выбираем строки, где поле «Сотрудник» = «Иванов И. И.»

    let

       source = Excel.CurrentWorkbook(){[Name = "Таблица2"]}[Content],

       cols_types = Table.TransformColumnTypes(

          source,

          {

             {"Дата", type datetime},

             {"Сотрудник", type text},

             {"Сумма сделки", Int64.Type},

             {"Количество", Int64.Type}

          }

       ),

       rows_select = Table.SelectRows(

          cols_types,

          each [Сотрудник] = "Иванов И. И."

       )

    in

       rows_select

    Анпивот и Анпивот других столбцов

    Анпивот — это превращение горизонтальной широкой таблицы в узкую вертикальную.

    Была такая таблица:

 

    А стала такая:

 

    Решение с помощью Table.Unpivot:

    let

       source = Excel.CurrentWorkbook(){[Name = "Таблица24"]}[Content],

       list_col_names_source = Table.ColumnNames(source),

       list_select = List.Select(list_col_names_source, each _ <> "Сотрудник"),

       tab_unpivot = Table.Unpivot(source, list_select, "Месяц", "Сумма")

    in

       tab_unpivot

    Решение с помощью Table.UnpivotOtherColumns:

    let

       source = Excel.CurrentWorkbook(){[Name = "Таблица24"]}[Content],

       tab_unpivot = Table.UnpivotOtherColumns(

          source,

          {"Сотрудник"},

          "Месяц",

          "Сумма"

       )

    in

       tab_unpivot

    Пивот

    Пивот — это операция обратная анпивоту, т. е. мы наоборот из узкой таблицы сделаем широкую.

    let

       source      = Unpivot,

       list_months = List.Distinct(source[Месяц]),

       tab_pivot   = Table.Pivot(source, list_months, "Месяц", "Сумма")

    in

       tab_pivot

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

    Table.TransformColumnTypes

    Int64.Type

    Table.RowCount

    Table.Group

    List.Sum

    GroupKind.Global

    GroupKind.Local

    Table.Sort

    Order.Ascending

    Order.Descending

    Table.SelectRows

    Table.ColumnNames

    List.Select

    Table.Unpivot

    Table.UnpivotOtherColumns

    List.Distinct

    Table.Pivot

    Этот урок входит в Продвинутый курс Power Query