Power Query Практический №3. Объединить множество, если заголовки не совпадают

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

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

• YouTube
• VK

Ссылки:

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

Описание

Нам нужно объединить таблицы по вертикали. Должна получиться одна длинная таблица. Заголовки у таблиц не совпадают, но порядок и количество столбцов всегда одинаковое. Мы разберем как правильно объединить таблицы по вертикали в таком случае.

Решение

При объединении таблиц нужно опустить заголовок таблиц в первую строку. Заголовки в таком случае Power Query задаст по умолчанию.

Мы объединим таблицы с заголовками по умолчанию, а потом в получившейся таблице задать заголовки вручную.

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

• Folder.Files
• Table.SelectColumns
• Table.AddColumn
• Csv.Document
• Table.ExpandTableColumn
• Table.TransformColumnTypes
• Int64.Type
• Table.RemoveRowsWithErrors
• Table.RenameColumns
• Excel.Workbook

Код

Объединяем CSV:

let
   source = Folder.Files(
      path&"Data CSV"
   ), 
   col_select = Table.SelectColumns(source, {"Content"}), 
   tab_add_col = Table.AddColumn(
      col_select, 
      "read_csv", 
      each Csv.Document([Content], [Delimiter = ","])
   ), 
   col_select_2 = Table.SelectColumns(tab_add_col, {"read_csv"}), 
   col_expand = Table.ExpandTableColumn(
      col_select_2, 
      "read_csv", 
      {
         "Column1", 
         "Column2", 
         "Column3", 
         "Column4", 
         "Column5", 
         "Column6", 
         "Column7", 
         "Column8", 
         "Column9"
      }, 
      {
         "Column1", 
         "Column2", 
         "Column3", 
         "Column4", 
         "Column5", 
         "Column6", 
         "Column7", 
         "Column8", 
         "Column9"
      }
   ), 
   cols_types = Table.TransformColumnTypes(
      col_expand, 
      {{"Column3", type date}, {"Column8", Int64.Type}}
   ), 
   cols_types_2 = Table.TransformColumnTypes(
      cols_types, 
      {
         {"Column4", type number}, 
         {"Column5", type number}, 
         {"Column6", type number}, 
         {"Column7", type number}, 
         {"Column9", type number}
      }, 
      "en-001"
   ), 
   rows_remove_errs = Table.RemoveRowsWithErrors(cols_types_2, {"Column3"}), 
   cols_rename = Table.RenameColumns(
      rows_remove_errs, 
      {
         {"Column1", "exchange"}, 
         {"Column2", "symbol"}, 
         {"Column3", "date"}, 
         {"Column4", "open"}, 
         {"Column5", "high"}, 
         {"Column6", "low"}, 
         {"Column7", "close"}, 
         {"Column8", "volume"}, 
         {"Column9", "adj_close"}
      }
   )
in
   cols_rename

Объединить XLSX:

let
   source = Folder.Files(path & "Data XLSX"), 
   col_select = Table.SelectColumns(source, {"Content"}), 
   tab_add_col = Table.AddColumn(
      col_select, 
      "read_excel", 
      each Excel.Workbook([Content])
   ), 
   col_transform = Table.TransformColumns(
      tab_add_col, 
      {{"read_excel", each _{[Name = "Лист1"]}[Data]}}
   ), 
   col_select_2 = Table.SelectColumns(col_transform, {"read_excel"}), 
   col_expand = Table.ExpandTableColumn(
      col_select_2, 
      "read_excel", 
      {
         "Column1", 
         "Column2", 
         "Column3", 
         "Column4", 
         "Column5", 
         "Column6", 
         "Column7", 
         "Column8", 
         "Column9"
      }, 
      {
         "Column1", 
         "Column2", 
         "Column3", 
         "Column4", 
         "Column5", 
         "Column6", 
         "Column7", 
         "Column8", 
         "Column9"
      }
   ), 
   cols_types = Table.TransformColumnTypes(
      col_expand, 
      {{"Column3", type date}, {"Column8", Int64.Type}}
   ), 
   cols_types_2 = Table.TransformColumnTypes(
      cols_types, 
      {
         {"Column4", type number}, 
         {"Column5", type number}, 
         {"Column6", type number}, 
         {"Column7", type number}, 
         {"Column9", type number}
      }, 
      "en-001"
   ), 
   rows_remove_errs = Table.RemoveRowsWithErrors(cols_types_2, {"Column3"}), 
   cols_rename = Table.RenameColumns(
      rows_remove_errs, 
      {
         {"Column1", "exchange"}, 
         {"Column2", "symbol"}, 
         {"Column3", "date"}, 
         {"Column4", "open"}, 
         {"Column5", "high"}, 
         {"Column6", "low"}, 
         {"Column7", "close"}, 
         {"Column8", "volume"}, 
         {"Column9", "adj_close"}
      }
   )
in
   cols_rename

Этот урок входит в Практический курс Power Query

Номер урока	Урок	Описание
1	Обработка типичной выписки	В этом уроке мы обработаем типичную банковскую выписку.
2	Интересная консолидация	В этом уроке мы будем практиковать объединение таблиц по вертикали.
3	Множественная консолидация при несовпадении заголовков	Объединим таблицы по вертикали, когда заголовки таблиц не совпадают, но порядок всегда одинаков.
4	Таблица дат	Для построения отчетов в модели данных Excel вам нужно создать таблицу дат. Сейчас вы узнаете как это сделать в Power Query.
5	Продажи год назад	В этом уроке мы узнаем как в Power Query получить таблицу с продажами прошлого года напротив текущих.
6	Консолидация книг и листов одновременно	В этом уроке мы научимся объединять все листы всех книг, т. е. выполним двухуровневую консолидацию.
7	Столбец общей суммы в PQ и PP	В этом уроке мы создадим столбец, в котором будет находиться общая сумма всех строк таблицы. Так же разберем эту же операцию в Power Pivot.
8	Скученные данные 3	В этом уроке мы обработаем еще один файл со скученными данными. На этот раз в одном столбце находятся даты и номенклатура. Нужно разбить этот столбец на два.
9	Строки преобразовать в столбцы	У нас есть таблица, в которой один столбец имеет скученные данные. Нам нужно скученный столбец преобразовать в столбцы.
10	Пивот, анпивот, группировка	В этом уроке мы попрактикуем анпивот, условную логику и группировку.
11	Нужные столбцы с нужного листа	В этом уроке мы научимся извлекать нужные столбцы с нужного листа не используя название самого листа.
12	Объединение, группировка, транспонирование, анпивот	В этом уроке на очень интересном примере из реальной жизни попрактикуем несколько техник Power Query: группировка, анпивот, объединение таблиц по горизонтали, транспонирование.
13	Нарастающий итог 4, много группировок	В этом уроке мы изучим еще 1 способ создать столбец нарастающего итога в Power Query. В отличие от других способов здесь мы не будем пользоваться формулами. Все сделаем при помощи пользовательского интерфейса.
14	Количество позиций в строке	В этом уроке мы посчитаем количество наименований, перечисленных в одной ячейке.
15	List Contains, Пользовательская функция	В этом уроке повторим создание пользовательских функций и рассмотрим еще 1 пример использования функции List.Contains.
16	ABC анализ	Научимся выполнять ABC анализ в Power Query.