Power Query. Ошибки. Как развернуть все столбцы

Описание

Например, вы создали запрос, в котором есть операция слияния двух запросов и разворот табличного столбца.

Периодически файлы-источники обновляются. Если в источнике появится один или несколько новых столбцов, то они не будут развернуты автоматически.

Если вы создали запрос только при помощи UI, то вы можете совершить ошибку и даже этого не заметить, потому что новые столбцы не будут развернуты автоматически.

Решение

Для решения этой задачи нам понадобятся функции:

• Table.ColumnNames
• List.Distinct
• Table.ToList
• Table.ExpandListColumn

Ключом к решению этой задачи является создание списка всех названий столбцов.

Для этого у нас есть функция Table.ColumnNames. К каждому источнику мы обратимся с помощью этой функции, а потом с помощью List.Distinct у нас получится список уникальных значений названий столбцов. Именно этот список мы и передадим функции, которая разворачивает табличный столбец.

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

• Table.SelectRows
• Table.SelectColumns
• Table.TransformColumns
• Text.AfterDelimiter
• Table.TransformColumnTypes
• Table.AddColumn
• Table.ColumnNames
• List.Distinct
• Table.ToList
• Table.ExpandListColumn
• Table.ExpandTableColumn
• Table.ReplaceValue
• Replacer.ReplaceText
• Table.Distinct
• Table.UnpivotOtherColumns
• Table.RenameColumns

Код

Первый способ:

let
   path = Excel.CurrentWorkbook(){[Name = "Путь"]}[Content][Column1]{0}, 
   source = Excel.Workbook(File.Contents(path), null, true), 
   rows_select = Table.SelectRows(source, each ([Kind] = "Table")), 
   cols_select = Table.SelectColumns(rows_select, {"Name", "Data"}), 
   col_transform = Table.TransformColumns(
      cols_select, 
      {{"Name", each Text.AfterDelimiter(_, "_"), type text}}
   ), 
   col_type = Table.TransformColumnTypes(
      col_transform, 
      {{"Name", type date}}
   ), 
   tab_add_col = Table.AddColumn(
      col_type, 
      "Пользовательская", 
      each Table.ColumnNames([Data])
   ), 
   list_unique = List.Distinct(
      Table.ToList(
         Table.ExpandListColumn(
            Table.SelectColumns(tab_add_col, {"Пользовательская"}), 
            "Пользовательская"
         )
      )
   ), 
   col_expand = Table.ExpandTableColumn(col_type, "Data", list_unique)
in
   col_expand

Второй способ:

let
   path = Excel.CurrentWorkbook(){[Name = "Путь"]}[Content][Column1]{0}, 
   source = Excel.Workbook(File.Contents(path), null, true), 
   rows_select = Table.SelectRows(source, each ([Kind] = "Table")), 
   cols_select = Table.SelectColumns(rows_select, {"Name", "Data"}), 
   col_replace_value = Table.ReplaceValue(
      cols_select, 
      "_", 
      "", 
      Replacer.ReplaceText, 
      {"Name"}
   ), 
   col_transform = Table.TransformColumns(
      col_replace_value, 
      {{"Name", each _ & ".01", type text}}
   ), 
   col_type = Table.TransformColumnTypes(
      col_transform, 
      {{"Name", type date}}
   ), 
   list_distinct = 
      let
         tab_add_col = Table.AddColumn(
            col_type, 
            "Названия", 
            each Table.ColumnNames([Data])
         ), 
         list_col = Table.SelectColumns(tab_add_col, "Названия"), 
         list_expand = Table.ExpandListColumn(list_col, "Названия"), 
         list_distinct = Table.Distinct(list_expand)
      in
         list_distinct, 
   col_expand = Table.ExpandTableColumn(
      col_type, 
      "Data", 
      list_distinct[Названия], 
      list_distinct[Названия]
   ), 
   tab_unpivot = Table.UnpivotOtherColumns(
      col_expand, 
      {"Name", "Posid", "Адрес"}, 
      "Атрибут", 
      "Значение"
   ), 
   col_transform_2 = Table.TransformColumns(
      tab_unpivot, 
      {{"Атрибут", each _ & ".План", type text}}
   ), 
   cols_rename = Table.RenameColumns(
      col_transform_2, 
      {{"Name", "Activation Date"}, {"Posid", "POSID"}}
   )
in
   cols_rename