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

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

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

• YouTube
• VK

Ссылки:

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

Описание

В этом уроке вы узнаете как объединить все таблицы, которые находятся в разных книгах 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

Этот урок входит в Базовый курс Power Query

Номер урока	Урок	Описание
1	Зачем нужен Power Query. Обзор возможностей	Этот урок сам по себе является мини-курсом. Здесь вы узнаете для каких видов операций с данными создан Power Query.
2	Подключение Excel	Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов.
3	Подключение CSV/TXT, таблиц, диапазонов	Подключаемся к к файлам CSV/TXT, Excel.
4	Объединить таблицы по вертикали	Учимся объединять две таблицы по вертикали — combine.
5	Объединить по вертикали все таблицы одной книги друг за другом	Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel.
6	Объединить по вертикали все файлы в папке	Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке.
7	Объединение таблиц по горизонтали	Учимся объединять таблицы по горизонтали — JOIN, merge.
8	Объединить таблицы с агрегированием	Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY.
9	Анпивот (Unpivot)	Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными.
10	Многоуровневый анпивот (Анпивот с подкатегориями)	Более сложный вариант Анпивота — в строках находится несколько измерений.
11	Скученные данные	Данные собраны в одном столбце, нужно правильно его разбить на несколько.
12	Скученные данные 2	Разбираем еще один пример скученных данных.
13	Ссылка на другую строку	Как сослаться на другую строку.
14	Ссылка на другую строку 2	Как сослаться на другую строку, используя объединение по горизонтали.
15	Виды объединения таблиц по горизонтали	Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN.
16	Виды объединения таблиц по горизонтали 2	Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN.
17	Группировка	Изучаем операцию группировки с агрегированием — GROUP BY.
18	Консолидация множества таблиц пользовательской функцией	Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции.
19	Деление на справочник и факт	Разделим один датасет на два датасета: справочник и факт.
20	Создание параметра	Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query.
21	Таблица параметров	Создадим целую таблицу параметров и будем их использовать в запросах Power Query.
22	Объединение таблиц по вертикали, когда не совпадают заголовки столбцов	Как объединить две таблицы по вертикали, если названия столбцов не совпадают.
23	Поиск ключевых слов	Научимся искать ключевые слова в текстовом поле.
24	Поиск ключевых слов 2	Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию.