Power Query. Основы. Объединить много таблиц по вертикали пользовательской функцией

Описание

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

Решение

В этом уроке мы изучим/повторим:

• Как объединить таблицы по вертикали
• Как объединить все таблицы в папке
• Пользовательские функции в Power Query
• Консолидация множества таблиц
• Параметры в Power Query

Сначала мы создадим запрос для обработки одного файла и преобразуем его в функцию. Параметром этой функции будет полный путь к файлу источнику.

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

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

• Folder.Files
• Table.SelectRows
• Table.TransformColumnTypes
• Table.AddColumn
• Text.BeforeDelimiter
• Int64.Type
• Table.RemoveRowsWithErrors
• Text.Contains
• Table.CombineColumns
• Table.SelectColumns
• Combiner.CombineTextByDelimiter
• QuoteStyle.None
• Table.TransformColumns
• Text.BetweenDelimiters
• RelativePosition.FromEnd
• Table.Sort
• Order.Ascending
• Text.AfterDelimiter
• Table.AddIndexColumn
• Table.ReorderColumns

Код

Итоговый запрос:

let
   source = Folder.Files(Путь), 
   rows_select_1 = Table.SelectRows(source, each ([Extension] = ".xlsx")), 
   tab_add_col_1 = Table.TransformColumnTypes(
      Table.AddColumn(
         rows_select_1, 
         "Name_Префикс", 
         each Text.BeforeDelimiter([Name], "_"), 
         type text
      ), 
      {{"Name_Префикс", Int64.Type}}
   ), 
   rows_remove_errs = Table.RemoveRowsWithErrors(
      tab_add_col_1, 
      {"Name_Префикс"}
   ), 
   rows_select_2 = Table.SelectRows(
      rows_remove_errs, 
      each not Text.Contains([Name], "Общая")
   ), 
   rows_select_3 = Table.CombineColumns(
      Table.SelectColumns(rows_select_2, {"Folder Path", "Name"}), 
      {"Folder Path", "Name"}, 
      Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
      "Путь"
   ), 
   tab_add_col_2_func = Table.AddColumn(
      rows_select_3, 
      "Ф_Путь", 
      each Ф_Путь([Путь])
   ), 
   col_transform = Table.TransformColumns(
      tab_add_col_2_func, 
      {
         {
            "Путь", 
            each Text.BetweenDelimiters(
               _, 
               "\", 
               ".", 
               {0, RelativePosition.FromEnd}, 
               0
            ), 
            type text
         }
      }
   ), 
   col_expand = Table.ExpandTableColumn(
      col_transform, 
      "Ф_Путь", 
      {
         "Дата", 
         "Агент", 
         "ФИО#(lf)льготника", 
         "Город", 
         "ТУ", 
         "Категория льготы"
      }
   ), 
   rows_select_4 = Table.SelectRows(col_expand, each ([Город] <> null)), 
   types = Table.TransformColumnTypes(
      rows_select_4, 
      {
         {"Дата", type date}, 
         {"Агент", Int64.Type}, 
         {"ФИО#(lf)льготника", type text}, 
         {"Город", type text}, 
         {"ТУ", Int64.Type}, 
         {"Категория льготы", type text}
      }
   ), 
   tab_sort = Table.Sort(
      types, 
      {{"Дата", Order.Ascending}, {"Путь", Order.Ascending}}
   )
in
   tab_sort

Запрос для проверки:

let
   source = Folder.Files(Путь), 
   rows_select_1 = Table.SelectRows(source, each ([Extension] = ".xlsx")), 
   col_transform = Table.TransformColumnTypes(
      Table.AddColumn(
         rows_select_1, 
         "Name_Префикс", 
         each Text.BeforeDelimiter([Name], "_"), 
         type text
      ), 
      {{"Name_Префикс", Int64.Type}}
   ), 
   rows_remove_errs = Table.RemoveRowsWithErrors(
      col_transform, 
      {"Name_Префикс"}
   ), 
   rows_select_2 = Table.SelectRows(
      rows_remove_errs, 
      each not Text.Contains([Name], "Общая")
   ), 
   cols_select = Table.CombineColumns(
      Table.SelectColumns(rows_select_2, {"Folder Path", "Name"}), 
      {"Folder Path", "Name"}, 
      Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
      "Путь"
   ), 
   cols_transform_2 = Table.TransformColumns(
      cols_select, 
      {
         {
            "Путь", 
            each Text.AfterDelimiter(_, "\", {0, RelativePosition.FromEnd}), 
            type text
         }
      }
   ), 
   col_rename = Table.RenameColumns(cols_transform_2, {{"Путь", "Файлы"}}), 
   tab_add_index = Table.AddIndexColumn(col_rename, "№", 1, 1), 
   tab_reorder_cols = Table.ReorderColumns(tab_add_index, {"№", "Файлы"})
in
   tab_reorder_cols