Power Query. Практика. Пивот, анпивот, группировка

Описание

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

Наши исходные данные выглядят следующим образом:

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

В результате мы должны получить следующую таблицу:

Решение

Сначала мы выполним операцию Анпивот, чтобы превратить длинную горизонтальную таблицу в нормальную вертикальную.

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

Далее следует операция группировки, где мы посчитаем количество выполненных и невыполненных дней.

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

• Excel.Workbook
• File.Contents
• Table.PromoteHeaders
• Table.UnpivotOtherColumns
• Table.TransformColumnTypes
• Int64.Type
• Table.RemoveRowsWithErrors
• Table.AddColumn
• Table.Group
• Table.RowCount
• Table.Pivot
• List.Distinct
• List.Sum

Код

let
   source = Excel.Workbook(File.Contents(Путь)),
   get_table = source{[Item = "План_факт сентябрь", Kind = "Sheet"]}[Data],
   table_promote_headers = Table.PromoteHeaders(
      get_table,
      [PromoteAllScalars = true]
   ),
   table_unpivot = Table.UnpivotOtherColumns(
      table_promote_headers,
      {"Дилер", "ТТ", "План"},
      "Месяц",
      "Результат"
   ),
   types = Table.TransformColumnTypes(
      table_unpivot,
      {
         {"Месяц", type date},
         {"План", Int64.Type},
         {"Результат", Int64.Type}
      }
   ),
   rows_remove_errs = Table.RemoveRowsWithErrors(types, {"Месяц"}),
   table_add_col = Table.AddColumn(
      rows_remove_errs,
      "Выполнено/Не выполнено",
      each if [Результат] < [План] then "Не выполнено" else "Выполнено"
   ),
   table_group = Table.Group(
      table_add_col,
      {"Дилер", "ТТ", "Выполнено/Не выполнено"},
      {{"Количество", each Table.RowCount(_), type number}}
   ),
   table_pivot = Table.Pivot(
      table_group,
      List.Distinct(table_group[#"Выполнено/Не выполнено"]),
      "Выполнено/Не выполнено",
      "Количество",
      List.Sum
   ),
   table_add_col_2 = Table.AddColumn(
      table_pivot,
      "Штраф",
      each if [Не выполнено] = null then null else "Штраф"
   )
in
   table_add_col_2