Power Query. Практика. Пользовательская функция и List.Contains

---

Описание

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

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

Решение

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

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

1. Подключение к листу Excel
2. Выбор определенной таблицы
3. Преобразование таблицы в лист

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

• Excel.CurrentWorkbook
• Table.TransformColumnTypes
• Table.ToList
• Excel.Workbook
• File.Contents
• Table.PromoteHeaders
• Int64.Type
• Table.AddColumn
• List.Contains
• Table.FillDown
• Table.SelectRows
• Table.RemoveColumns
• Table.ReorderColumns

Код

Код функции:

(column) =>
   let
      source = Excel.CurrentWorkbook(),
      sales_point = source{[Name = column]}[Content],
      types = Table.TransformColumnTypes(
         sales_point,
         {{Table.ColumnNames(sales_point){0}, type text}}
      ),
      table_to_list = Table.ToList(types)
   in
      table_to_list

Код итогового запроса:

let
   path = Excel.CurrentWorkbook(){[Name = "Путь"]}[Content]{0}[Column1],
   source = Excel.Workbook(
      File.Contents(path & "\Данные.xlsx"),
      null,
      true
   ),
   get_data = source{[Item = "Данные", Kind = "Sheet"]}[Data],
   table_promote_headers = Table.PromoteHeaders(
      get_data,
      [PromoteAllScalars = true]
   ),
   types = Table.TransformColumnTypes(
      table_promote_headers,
      {{"Названия строк", type text}, {"Сумма", Int64.Type}}
   ),
   table_add_col_1 = Table.AddColumn(
      types,
      "ТТ",
      each 
         if List.Contains(Ф_Выбор("ТТ"), [Названия строк]) then
            [Названия строк]
         else
            null
   ),
   table_add_col_2 = Table.AddColumn(
      table_add_col_1,
      "Канал",
      each 
         if List.Contains(Ф_Выбор("Канал"), [Названия строк]) then
            [Названия строк]
         else
            null
   ),
   table_add_col_3 = Table.AddColumn(
      table_add_col_2,
      "Продукт",
      each 
         if List.Contains(Ф_Выбор("Продукт"), [Названия строк]) then
            [Названия строк]
         else
            null
   ),
   cols_fill = Table.FillDown(table_add_col_3, {"ТТ", "Канал"}),
   rows_select = Table.SelectRows(cols_fill, each ([Продукт] <> null)),
   cols_select = Table.RemoveColumns(rows_select, {"Названия строк"}),
   table_reorder_cols = Table.ReorderColumns(
      cols_select,
      {"ТТ", "Канал", "Продукт", "Сумма"}
   )
in
   table_reorder_cols