Power Query. Практика. Нужные столбцы нужной книги

Описание

У нас есть файл, из которого нужно извлечь нужные столбцы с определенного листа.

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

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

Решение

Чтобы найти среди листов Excel лист с заголовком «Выручка» мы создадим условный столбец, который проверит наличие этого слова в заголовках.

Дальше нам останется лишь привести полученную таблицу к нужному виду.

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

• Folder.Files
• Excel.Workbook
• Table.AddColumn
• List.ContainsAny
• Record.ToList
• List.Select
• Text.Contains
• Comparer.OrdinalIgnoreCase
• Table.SelectRows
• Table.Skip
• Table.PromoteHeaders
• List.Select
• Table.ColumnNames
• Table.SelectColumns
• Table.TransformColumnTypes
• Int64.Type
• Table.ReplaceErrorValues
• List.Transform

Код

В первом запросе мы подключимся к папке и получим перечень доступных источников:

let
   path = Excel.CurrentWorkbook(){[Name = "Параметры"]}[Content]{
      [Параметр = "Путь"]
   }[Значение],
   source = Folder.Files(path)
in
   source

Решаем задачу:

let
   get_book = Excel.Workbook(source{[Name = "И_KPI.xlsx"]}[Content]),
   table_add_col = Table.AddColumn(
      get_book,
      "Записи",
      each List.ContainsAny(
         Record.ToList([Data]{0}),
         List.Select(
            Record.ToList([Data]{0}),
            each Text.Contains(_, "выручка", Comparer.OrdinalIgnoreCase)
         )
      )
   ),
   rows_select = Table.SelectRows(table_add_col, each ([Записи] = true)),
   get_table = rows_select[Data]{0},
   rows_skip = Table.Skip(get_table, 1),
   table_headers = Table.PromoteHeaders(rows_skip),
   list_headers = List.Select(
      Table.ColumnNames(table_headers),
      each Text.Contains(_, "выручк", Comparer.OrdinalIgnoreCase)
   ),
   cols_select = Table.SelectColumns(
      table_headers,
      {"Адрес ТТ", "Код ТТ"} & list_headers
   ),
   types = Table.TransformColumnTypes(
      cols_select,
      {
         {"Код ТТ", Int64.Type},
         {"План по выручке", type number},
         {"Факт по выручке", type number},
         {"Прогноз по выручке", type number},
         {"%вып по выручке", type number},
         {"План по выручке 3М", type number},
         {"Факт по выручке 3М", type number},
         {"Прогноз по выручке 3М", type number},
         {"%вып по выручке 3М", type number}
      }
   ),
   table_replace_errors = Table.ReplaceErrorValues(
      types,
      List.Transform(Table.ColumnNames(types), each {_, 0})
   )
in
   table_replace_errors