Power Query Продвинутый №24. Разгруппировка, Скрытые возможности Table.ReplaceValue

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

Все уроки Продвинутого курса Power Query на этой странице и в этом плейлисте.

Описание

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

В данном примере нужно разбить суммы на 9 равных частей, указанные в столбец [Дней]. По одной для каждого дня.

Решение

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

Для решения задачи по разгруппировке в Power Query нам понадобятся функции:

• Table.ReplaceValue
• List.Dates
• Date.From
• #duration

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

• Table.SelectRows
• Text.Contains
• Table.AddColumn
• Table.ColumnNames
• Table.ToList
• Table.Distinct
• Table.ExpandListColumn
• Table.ExpandTableColumn
• Table.ReplaceValue
• Replacer.ReplaceValue
• Table.TransformColumnTypes
• List.Dates
• #duration

Код

Код подключения и подготовки таблицы:

let
   source = Excel.CurrentWorkbook(), 
   rows_select = Table.SelectRows(
      source, 
      each not Text.Contains([Name], "!") and [Name] <> "Командировка_Офис"
   ), 
   cols_select = Table.SelectColumns(rows_select, {"Content"}), 
   tab_add_col = Table.AddColumn(
      cols_select, 
      "Пользовательская", 
      each Table.ColumnNames([Content])
   ), 
   col_expand = Table.ToList(
      Table.Distinct(
         Table.ExpandListColumn(
            Table.SelectColumns(tab_add_col, {"Пользовательская"}), 
            "Пользовательская"
         )
      )
   ), 
   cols_select_2 = Table.SelectColumns(tab_add_col, "Content"), 
   col_expand_2 = Table.ExpandTableColumn(
      cols_select_2, 
      "Content", 
      col_expand, 
      col_expand
   ), 
   rows_select_2 = Table.SelectRows(
      col_expand_2, 
      each ([#"Розница/Офис"] <> null)
   )
in
   rows_select_2

Код разгруппировки:

let
   source = Table.SelectRows(
      Командировка_Подключение, 
      each [#"Розница/Офис"] = "Офис"
   ), 
   col_replace = Table.ReplaceValue(
      source, 
      each [Командировка Бух], 
      each [Командировка Бух] / [Дней], 
      Replacer.ReplaceValue, 
      {"Командировка Бух"}
   ), 
   col_replace_2 = Table.ReplaceValue(
      col_replace, 
      each [Командировка Упр], 
      each [Командировка Упр] / [Дней], 
      Replacer.ReplaceValue, 
      {"Командировка Упр"}
   ), 
   cols_types = Table.TransformColumnTypes(
      col_replace_2, 
      {
         {"Розница/Офис", type text}, 
         {"ФИО", type text}, 
         {"Город", type text}, 
         {"Дата начала", type date}, 
         {"Дней", Int64.Type}, 
         {"Командировка Бух", type number}, 
         {"Командировка Упр", type number}
      }
   ), 
   tab_add_list_col = Table.AddColumn(
      cols_types, 
      "Дата", 
      each List.Dates([Дата начала], [Дней], #duration(1, 0, 0, 0))
   ), 
   cols_expand = Table.ExpandListColumn(tab_add_list_col, "Дата"), 
   cols_select = Table.SelectColumns(
      cols_expand, 
      {
         "Розница/Офис", 
         "ФИО", 
         "Город", 
         "Дата", 
         "Командировка Бух", 
         "Командировка Упр"
      }
   ), 
   cols_types_2 = Table.TransformColumnTypes(
      cols_select, 
      {{"Дата", type date}}
   )
in
   cols_types_2

Этот урок входит в Продвинутый курс Power Query