Описание

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

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

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

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

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

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

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

Посчитать количество строк в таблице с 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