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

---

Описание

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

В этом видео вы узнаете:

• Как объединить все таблицы в одной папке с Power Query
• Как сделать консолидацию всех файлов в папке в Excel
• Как объединить по вертикали все файлы в одной папке

Решение

Разберем 2 примера. Первым пример будет простым. Мы объединим файлы без предварительной обработки.

Втором пример будет немного посложнее. Мы объединим файлы с предварительной обработкой, но будет использовать только пользовательский интерфейс.

Объединить файлы из одной папки без предварительной обработки

Если предварительная обработка не требуется, то задача решается в 2 логических этапа:

1. На первом этапе мы подключимся к папке и оставим только нужный нам столбец и строки с необходимыми данными
2. Развернем табличный столбец и почистим данные

Объединить файлы из одной папки с предварительной обработкой

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

Сначала нужно подключиться к папке с файлами и развернуть столбец Content, нажав на кнопку:

После нажатия на кнопку Power Query автоматически создаст запросы, функции и параметры:

Все, что вы проделаете с запросом «Пример файла» автоматически применится к каждому файлу в папке.

То, что находится в данном примере находится в запросе sales — это итоговая результирующая таблица.

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

• Folder.Files
• Table.SelectColumns
• Table.AddColumn
• Csv.Document
• Table.ExpandTableColumn
• Table.PromoteHeaders
• Table.RemoveRowsWithErrors
• Table.TransformColumnTypes
• Int64.Type
• Table.Skip
• Table.SelectRows
• Table.RenameColumns
• Table.ColumnNames
• Excel.CurrentWorkbook

Код

Без предварительной обработки

let
   // Подключаемся к папке и выбираем файлы для объединения
   source = Folder.Files(path & "Котировки csv"),
   cols_select_1 = Table.SelectColumns(source, {"Content", "Name"}),
   col_add = Table.AddColumn(cols_select_1, "Таблица", each Csv.Document([Content])),
   cols_select_2 = Table.SelectColumns(col_add, {"Таблица"}),

   // Развернуть табличный столбец и почистить данные
   col_expand = Table.ExpandTableColumn(
      cols_select_2,
      "Таблица",
      {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"},
      {
         "Таблица.Column1",
         "Таблица.Column2",
         "Таблица.Column3",
         "Таблица.Column4",
         "Таблица.Column5",
         "Таблица.Column6",
         "Таблица.Column7"
      }
   ),
   headers_promote = Table.PromoteHeaders(col_expand, [PromoteAllScalars = true]),
   rows_remove_errors = Table.RemoveRowsWithErrors(headers_promote, {"Date"}),
   types_1 = Table.TransformColumnTypes(
      rows_remove_errors,
      {{"Date", type date}, {"Volume", Int64.Type}}
   ),
   types_2 = Table.TransformColumnTypes(
      types_1,
      {
         {"Open", type number},
         {"High", type number},
         {"Low", type number},
         {"Close", type number},
         {"Adj Close", type number}
      },
      "en-US"
   )
in
   types_2

С предварительной обработкой

Код «Пример файла»:

let
   source      = Folder.Files(path_folder),
   rows_select = Table.SelectRows(source, each ([Extension] = ".txt")),
   get_file    = rows_select{0}[Content]
in
   get_file

Код «Параметр файла примера1»:

#"Пример файла"
   meta [
      IsParameterQuery         = true,
      BinaryIdentifier         = #"Пример файла",
      Type                     = "Binary",
      IsParameterQueryRequired = true
   ]

Код «Преобразовать пример файла из Продажи»:

let
   source = Csv.Document(
      #"Параметр файла примера1",
      [Delimiter = "	", Columns = 26, Encoding = 65001, QuoteStyle = QuoteStyle.None]
   ),
   rows_skip = Table.Skip(source, 4),
   headers = Table.PromoteHeaders(rows_skip, [PromoteAllScalars = true]),
   rows_select = Table.SelectRows(headers, each ([Дата] <> "Итого"))
in
   rows_select

Код «Преобразовать файл из Продажи»:

let
   fn_append = (#"Параметр файла примера1" as binary) =>
      let
         source = Csv.Document(
            #"Параметр файла примера1",
            [
               Delimiter  = "	",
               Columns    = 26,
               Encoding   = 65001,
               QuoteStyle = QuoteStyle.None
            ]
         ),
         rows_skip = Table.Skip(source, 4),
         headers = Table.PromoteHeaders(rows_skip, [PromoteAllScalars = true]),
         rows_select = Table.SelectRows(headers, each ([Дата] <> "Итого"))
      in
         rows_select
in
   fn_append

Код результирующей таблицы:

let
   source = Folder.Files(path_folder & "Продажи\"),
   fn_append = Table.AddColumn(
      source,
      "Преобразовать файл из Продажи",
      each #"Преобразовать файл из Продажи"([Content])
   ),
   cols_rename = Table.RenameColumns(
      fn_append,
      {"Name", "Source.Name"}
   ),
   cols_select = Table.SelectColumns(
      cols_rename,
      {"Source.Name", "Преобразовать файл из Продажи"}
   ),
   col_expand = Table.ExpandTableColumn(
      cols_select,
      "Преобразовать файл из Продажи",
      Table.ColumnNames(#"Преобразовать файл из Продажи"(#"Пример файла"))
   )
in
   col_expand