Power Query. Основы. Многоуровневый анпивот, анпивот с подкатегориями

Описание

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

Решение

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

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

После этого мы выполним анпивот и почистим получившуюся таблицу.

В этом уроке вы научитесь:

• Как сделать анпипивот с подкатегориями в Power Query
• Как сделать многоуровневый анпивот в Power Query

Мы применим следующие техники:

• Транспонирование таблицы
• Заполнение значений вниз
• Объединение (конкатенация) столбцов
• Анпивот
• Разделение столбцов
• Сортировка
• Добавление префикса и суффикса к тексту

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

• Table.FillDown
• Table.Transpose
• Table.CombineColumns
• Combiner.CombineTextByDelimiter
• QuoteStyle.None
• Table.PromoteHeaders
• Table.UnpivotOtherColumns
• Table.SplitColumn
• Splitter.SplitTextByDelimiter
• Table.TransformColumnTypes
• Table.SelectRows
• Table.TransformColumns
• Table.Sort
• Order.Ascending
• Table.RenameColumns

Код

let
   source = Excel.CurrentWorkbook(), 
   get_table = source{[Name = "Свод"]}[Content], 
   col_fill_down = Table.FillDown(get_table, {"Column1"}), 
   tab_transpose = Table.Transpose(col_fill_down), 
   col_fill_down_2 = Table.FillDown(tab_transpose, {"Column1"}), 
   cols_combine = Table.CombineColumns(
      col_fill_down_2, 
      {"Column1", "Column2"}, 
      Combiner.CombineTextByDelimiter("---", QuoteStyle.None), 
      "Столбец"
   ), 
   tab_transpose_2 = Table.Transpose(cols_combine), 
   tab_promote_headers = Table.PromoteHeaders(
      tab_transpose_2, 
      [PromoteAllScalars = true]
   ), 
   tab_unpivot = Table.UnpivotOtherColumns(
      tab_promote_headers, 
      {"---Номенклатура1", "---Номенклатура2"}, 
      "Атрибут", 
      "Значение"
   ), 
   col_split = Table.SplitColumn(
      tab_unpivot, 
      "Атрибут", 
      Splitter.SplitTextByDelimiter("---", QuoteStyle.Csv), 
      {"Месяц", "Показатель"}
   ), 
   types = Table.TransformColumnTypes(
      col_split, 
      {
         {"Месяц", type text}, 
         {"Показатель", type text}, 
         {"---Номенклатура1", type text}, 
         {"---Номенклатура2", type text}, 
         {"Значение", type number}
      }
   ), 
   rows_select = Table.SelectRows(
      types, 
      each ([Показатель] <> "") and ([#"---Номенклатура2"] <> null)
   ), 
   col_prefix = Table.TransformColumns(
      rows_select, 
      {{"Месяц", each "1 " & _, type text}}
   ), 
   col_suffix = Table.TransformColumns(
      col_prefix, 
      {{"Месяц", each _ & " 2019", type text}}
   ), 
   types_2 = Table.TransformColumnTypes(col_suffix, {{"Месяц", type date}}), 
   tab_sort = Table.Sort(
      types_2, 
      {
         {"Месяц", Order.Ascending}, 
         {"---Номенклатура2", Order.Ascending}, 
         {"Показатель", Order.Ascending}
      }
   ), 
   cols_rename = Table.RenameColumns(
      tab_sort, 
      {
         {"---Номенклатура1", "Категория"}, 
         {"---Номенклатура2", "Подкатегория"}
      }
   )
in
   cols_rename