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

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

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

• YouTube
• VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

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

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

Решение

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

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

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

Этот урок входит в Практический курс Power Query

Номер урока	Урок	Описание
1	Обработка типичной выписки	В этом уроке мы обработаем типичную банковскую выписку.
2	Интересная консолидация	В этом уроке мы будем практиковать объединение таблиц по вертикали.
3	Множественная консолидация при несовпадении заголовков	Объединим таблицы по вертикали, когда заголовки таблиц не совпадают, но порядок всегда одинаков.
4	Таблица дат	Для построения отчетов в модели данных Excel вам нужно создать таблицу дат. Сейчас вы узнаете как это сделать в Power Query.
5	Продажи год назад	В этом уроке мы узнаем как в Power Query получить таблицу с продажами прошлого года напротив текущих.
6	Консолидация книг и листов одновременно	В этом уроке мы научимся объединять все листы всех книг, т. е. выполним двухуровневую консолидацию.
7	Столбец общей суммы в PQ и PP	В этом уроке мы создадим столбец, в котором будет находиться общая сумма всех строк таблицы. Так же разберем эту же операцию в Power Pivot.
8	Скученные данные 3	В этом уроке мы обработаем еще один файл со скученными данными. На этот раз в одном столбце находятся даты и номенклатура. Нужно разбить этот столбец на два.
9	Строки преобразовать в столбцы	У нас есть таблица, в которой один столбец имеет скученные данные. Нам нужно скученный столбец преобразовать в столбцы.
10	Пивот, анпивот, группировка	В этом уроке мы попрактикуем анпивот, условную логику и группировку.
11	Нужные столбцы с нужного листа	В этом уроке мы научимся извлекать нужные столбцы с нужного листа не используя название самого листа.
12	Объединение, группировка, транспонирование, анпивот	В этом уроке на очень интересном примере из реальной жизни попрактикуем несколько техник Power Query: группировка, анпивот, объединение таблиц по горизонтали, транспонирование.
13	Нарастающий итог 4, много группировок	В этом уроке мы изучим еще 1 способ создать столбец нарастающего итога в Power Query. В отличие от других способов здесь мы не будем пользоваться формулами. Все сделаем при помощи пользовательского интерфейса.
14	Количество позиций в строке	В этом уроке мы посчитаем количество наименований, перечисленных в одной ячейке.
15	List Contains, Пользовательская функция	В этом уроке повторим создание пользовательских функций и рассмотрим еще 1 пример использования функции List.Contains.
16	ABC анализ	Научимся выполнять ABC анализ в Power Query.