MyTetra Share
Делитесь знаниями!
Power Query. Формулы М. Группировка, Пивот, Анпивот, Сортировка
Время создания: 11.11.2021 21:31
Текстовые метки: Power Query, Формулы М
Раздел: !Закладки - MSO - Excel - Power Query
Запись: adgaver/mytetra_base_New/master/base/16366554806nd7fe69v4/text.html на raw.githubusercontent.com

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

Так же в этом разделе:
 
MyTetra Share v.0.58
Яндекс индекс цитирования