Power Query. Формулы М. Нюансы консолидации и List.PositionOf

Описание

В этом уроке мы решим несколько нестандартных задач по объединению таблиц по вертикали.

Разберемся как объединить таблицы из разных листов Excel с лишними строками вначале листа

В этой задаче на разных листах Excel находятся данные по продажам товаров из разных категорий. Нам нужно для заголовка категории создать отдельный столбец. Дополнительная сложность состоит в том, что перед данными находится несколько лишних строк и на разных листах количество лишних строк разное.

В итоге мы должны получить следующую таблицу:

Решение

Сначала нужно подключиться к книге-источнику, оставив только столбец с данными.

Дальше с помощью функции List.PositionOf в первом столбце мы найдем позицию ячейки со значением «Parent Category».

Далее создадим дубликат столбца «Data», в котором пропустим лишние строки с помощью функции Table.Skip.

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

• Excel.Workbook
• File.Contents
• Table.SelectColumns
• Table.AddColumn
• List.PositionOf
• Table.PromoteHeaders
• Table.RemoveColumns
• Table.ExpandTableColumn

Код

let
   source = Excel.Workbook(File.Contents(Путь & "Товары.xlsx"), null, true), 
   cols_select_1 = Table.SelectColumns(source, {"Data"}), 
   tab_add_col_1 = Table.AddColumn(
      cols_select_1, 
      "Категория", 
      each [Data]
         {List.PositionOf([Data][Column1], "Parent Category") + 1}
         [Column1], 
      type text
   ), 
   tab_add_col_2 = Table.AddColumn(
      tab_add_col_1, 
      "Таблица", 
      each Table.PromoteHeaders(
         Table.Skip([Data], List.PositionOf([Data][Column1], "Name")), 
         [PromoteAllScalars = true]
      )
   ), 
   cols_select_2 = Table.RemoveColumns(tab_add_col_2, {"Data"}), 
   col_expand = Table.ExpandTableColumn(
      cols_select_2, 
      "Таблица", 
      {
         "Name", 
         "ProductNumber", 
         "Color", 
         "StandardCost", 
         "ListPrice", 
         "Size"
      }, 
      {
         "Name", 
         "ProductNumber", 
         "Color", 
         "StandardCost", 
         "ListPrice", 
         "Size"
      }
   )
in
   col_expand

Наши инструменты для решения задачи в Power Query

В этом уроке мы изучим или вспомним следующее:

• Объединение таблиц из разных книг
• Объединение таблиц из разных листов
• Функция List.PositionOf
• Функция List.Last
• Функция Table.ColumnNames