Power Query Power Query. Базовый курс Основы

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

Все уроки Базового курса 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

Этот урок входит в Базовый курс 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	Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию.

предыдущая запись

Power Query. Основы. Таблица параметров

следующая запись