Power Query Практический №6. Консолидация книг и листов одновременно

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

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

• YouTube
• VK

Ссылки:

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

Описание

В этом уроке мы научимся как консолидировать или объединять по вертикали листы, которые находятся в разных книгах Excel.

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

Решение

Первым делом мы подключимся к папке, в которой находятся нужные книги Excel. Далее листы нам сначала придется еще отфильтровать, потому что в книгах помимо листов с нужными данными находятся и ненужные.

В данном случае мы должны получить только те листы, в которых в первой ячейке находится слово «число». Как раз для таких случаев существует функция Table.First.

Далее останется лишь немного почистить данные.

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

• Folder.Files
• Table.AddColumn
• Excel.Workbook
• Table.SelectColumns
• Table.ExpandTableColumn
• Table.SelectRows
• Table.First
• Table.PromoteHeaders
• Table.TransformColumnTypes
• Table.RemoveRowsWithErrors

Код

let
   source = Folder.Files(Путь),
   table_add_col = Table.AddColumn(
      source,
      "Подключение Excel",
      each Excel.Workbook([Content])
   ),
   cols_select = Table.SelectColumns(table_add_col, {"Подключение Excel"}),
   col_expand = Table.ExpandTableColumn(
      cols_select,
      "Подключение Excel",
      {"Name", "Data", "Item", "Kind", "Hidden"},
      {"Name", "Data", "Item", "Kind", "Hidden"}
   ),
   rows_select = Table.SelectRows(
      col_expand,
      each ([Hidden] = false) and ([Kind] = "Sheet")
   ),
   table_add_col_2 = Table.AddColumn(
      rows_select,
      "Пользовательская",
      each Table.First([Data])[Column1]
   ),
   rows_select_2 = Table.SelectRows(
      table_add_col_2,
      each ([Пользовательская] = "число")
   ),
   table_add_col_3 = Table.AddColumn(
      rows_select_2,
      "Таблица",
      each Table.PromoteHeaders([Data])
   ),
   cols_select_2 = Table.SelectColumns(table_add_col_3, {"Таблица"}),
   col_expand_2 = Table.ExpandTableColumn(
      cols_select_2,
      "Таблица",
      {"число", "км проехал за день ", "расход, л", "заправка, ДТ, л"},
      {"число", "км проехал за день ", "расход, л", "заправка, ДТ, л"}
   ),
   types = Table.TransformColumnTypes(
      col_expand_2,
      {
         {"число", type date},
         {"км проехал за день ", type number},
         {"расход, л", type number},
         {"заправка, ДТ, л", type number}
      }
   ),
   rows_remove_errs = Table.RemoveRowsWithErrors(types, {"число"}),
   rows_select_3 = Table.SelectRows(
      rows_remove_errs,
      each [число] <> null and [число] <> ""
   )
in
   rows_select_3

Этот урок входит в Практический курс 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.