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

Файлы к уроку: Файлы доступны только спонсорам канала. Оформите спонсорскую подписку, если хотите получить максимум от обучения. Стать спонсором канала: https://www.youtube.com/channel/UCwAru3vDmH-IdYtwK7JWN4w/join

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

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

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

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

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

Код функции будет такой:

(column)=>
let
    Источник = Excel.CurrentWorkbook(),
    ТТ = Источник{[Name=column]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(ТТ,{{Table.ColumnNames(ТТ){0}, type text}}),
    Пользовательская1 = Table.ToList(#"Измененный тип")
in
    Пользовательская1

В самом запросе к таблице-источнику мы вызовем эту функцию 3 раза: для ТТ, Канала продаж, Продукта.

Код вызова функции будет выглядеть так:

#"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "ТТ", each if List.Contains(Ф_Выбор("ТТ"), [Названия строк]) then [Названия строк] else null)

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

Полный код запроса будет таким:

let
    Путь = Excel.CurrentWorkbook(){[Name = "Путь"]}[Content]{0}[Column1],
    Источник = Excel.Workbook(File.Contents(Путь&"\Данные.xlsx"), null, true),
    Данные_Sheet = Источник{[Item="Данные",Kind="Sheet"]}[Data],
    #"Повышенные заголовки" = Table.PromoteHeaders(Данные_Sheet, [PromoteAllScalars=true]),
    #"Измененный тип" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Названия строк", type text}, {"Сумма", Int64.Type}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "ТТ", each if List.Contains(Ф_Выбор("ТТ"), [Названия строк]) then [Названия строк] else null),
    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Добавлен пользовательский объект", "Канал", each if List.Contains(Ф_Выбор("Канал"), [Названия строк]) then [Названия строк] else null),
    #"Добавлен пользовательский объект2" = Table.AddColumn(#"Добавлен пользовательский объект1", "Продукт", each if List.Contains(Ф_Выбор("Продукт"), [Названия строк]) then [Названия строк] else null),
    #"Заполнение вниз" = Table.FillDown(#"Добавлен пользовательский объект2",{"ТТ", "Канал"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Заполнение вниз", each ([Продукт] <> null)),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Строки с примененным фильтром",{"Названия строк"}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Удаленные столбцы",{"ТТ", "Канал", "Продукт", "Сумма"})
in
    #"Переупорядоченные столбцы"