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

Номер урока	Урок	Описание
1	Введение ч. 1	В этом уроке вы вкратце узнаете как устроен язык M / Power Query
2	Введение ч. 2	В этом уроке вы узнаете про ключевые слова let, in и узнаете как устроены функции M / Power Query.
3	Введение ч. 3	В этом уроке я расскажу какие преимущества вам даст владение языком формул M / Power Query
4	Объекты Power Query	В этом уроке мы познакомимся с объектами Power Query: Table, List, Record.
5	Объект List ч. 1	В этом уроке мы начнем знакомиться с объектом List в Excel Power Query.
6	Объект List ч. 2	Вы узнаете как создавать листы, как ссылаться на элементы листа и изучим несколько полезных формул листа.
7	Объект Record (Запись)	Record — это набор пар ключ-значение. Каждый Record — это строка таблицы.
8	Объект Table	В этом уроке мы познакомимся с объектом Table в Power Query. Что это такое и какие основные функции применяются к этому объекту.
9	Ссылки ч. 1	В этом уроке из курса по Power Query мы повторим как ссылаться на строки таблицы.
10	Ссылки ч. 2	В этом уроке из курса обучения Power Query будем практиковать ссылки.
11	Ссылки ч. 3	В этом уроке курса по Power Query мы повторим ссылки на запись, ссылки на значение, ссылки на другую строку.
12	Ссылки ч. 4	В этом уроке мы повторим/научимся ссылаться на ячейку, которая находится в другом шаге запроса.
13	Пользовательские функции	В этом уроке мы научимся создавать пользовательские функции.
14	Работа с датой и временем, #datetime, #duration	В этом уроке по Excel Power Query вы научитесь пользоваться функциями #duration и #datetime для создания даты/времени и длительности.
15	Аналоги текстовых функций Excel	В этом уроке курса мы изучим Power Query аналоги текстовых функций Excel.
16	Группировка, Пивот, Анпивот, Сортировка	В этом уроке по формулам Power Query мы разберем функции группировки, сведения, отмены сведения, сортировки и фильтра.
17	Нюансы консолидации из разных книг/листов Excel и List PositionOf	В этом уроке мы узнаем о некоторых нюансах объединения таблиц по вертикали из разных файлов.
18	Разделить столбец на строки, Text.Trim	В этом уроке мы разделим текстовый столбец на строки. Данные скучены в одной ячейке. Нужно разбить ее на несколько.
19	Заголовки в двух строках, List.Zip	В этом уроке мы обработаем заголовки, которые находятся в двух строках. Нам понадобится функция List.Zip
20	Вычисления в Power Query	В этом уроке мы научимся выполнять вычисления в Power Query и в Power Pivot.
21	Скользящее среднее, List Range	В этом уроке мы научимся считать скользящее среднее в Power Query.
22	Агрегирование текста, группировка	В этом уроке вы научитесь выполнять группировку с агрегированием текстовых значений в Power Query.
23	Table.TransformColumns	В этом уроке мы изучим функцию Table.TransformColumns.
24	Разгруппировка/Скрытые возможности Table.ReplaceValue	В этом уроке мы научимся делать разгруппировку данных в Power Query, а также изучим скрытые возможности функции Table.ReplaceValue.
25	Функция List.Accumulate	В этом уроке мы с нуля разберем функцию List.Accumulate. Сначала я объясню цикл For, а потом мы закрепим знания на несложных, но очень интересных примерах.
26	Множественная замена текста c List Accumulate и VBA	В этом уроке мы научимся делать множественную текстовую замену с помощью Power Query.
27	Функции Text.Format, List.Generate, List.Accumulate	В этом уроке мы сгенерируем список веб-страниц, в которых будет меняться параметр id.
28	Множественная текстовая замена c List.Generate	В этом уроке мы научимся выполнять множественную текстовую замену в Power Query с помощью функции List.Generate.
29	List.Generate 2, график платежей по кредиту	Создадим при помощи этой функции целую таблицу, которая будет представлять из себя график платежей по кредиту.
30	List.Generate, Нарастающий итог 5	Выполним нарастающий итог, который в сотни раз быстрее предыдущих версий нарастающего итога.