Сборка разноформатных таблиц с одного листа в Power Query

Постановка задачи

В качестве входных данных имеем Excel-файл, где на одном из листов расположены друг под другом несколько таблиц с данными по продажам следующего вида:

Исходные данные

Обратите внимание, что:

  • Таблицы разного размера и с разными наборами товаров и регионов по строчкам-столбцам без какой-либо сортировки.
  • Между таблицами могут быть вставлены пустые строки.
  • Количество таблиц может быть любым.
Два важных допущения. Предполагается, что:
  • Над каждой таблицей в первом столбце есть имя менеджера, чьи продажи иллюстрирует таблица (Иванов, Петров, Сидоров и т.д.)
  • Названия товаров и регионов во всех таблицах написаны одинаково - с точностью до регистра.

Конечная цель - собрать данные из всех таблиц в одну плоскую нормированную таблицу, удобную для последующего анализа и построения сводной, т.е. вот в такую:

Готовый результат

Шаг 1. Подключаемся к файлу

Создадим новый пустой файл Excel и в нём выберем на вкладке Данные команду Получить данные - Из файла - Из книги (Data - From file - From workbook). Укажем местоположение исходного файла с данными по продажам и затем в окне навигатора выберем нужный нам лист и нажмём на кнопку Преобразовать данные (Transform Data):

Выбираем лист в Навигаторе

В итоге все данные с него должны загрузиться в редактор Power Query:

Загруженные данные в Power Query

Шаг 2. Убираем мусор

Удалим автоматически созданные шаги Измененный тип (Changed Type) и Повышенные заголовки (Promoted Headers) и избавимся от пустых строк и строк с итогами с помощью фильтра null и ИТОГО по первому столбцу. В итоге получим следующую картинку:

Очищенные данные

Шаг 3. Добавляем менеджеров

Чтобы понимать впоследствии где чьи продажи, необходимо добавить к нашей таблице столбец, где в каждой строке будет соответствующая фамилия. Для этого:

1. Добавим вспомогательный столбец с нумерацией строк с помощью команды Добавление столбца - Столбец индекса - От 0 (Add column - Index column - From 0).

2. Добавим столбец с формулой командой Добавление столбца - Настраиваемый столбец (Add column - Custom column) и введём туда следующую конструкцию:

Вычисляемый столбец с менеджерами

Логика этой формулы проста - если в первом столбце значение очередной ячейки равно "Товар", то это значит, что мы наткнулись на начало новой таблицы, поэтому выводим значение предыдущей ячейки с именем менеджера. В противном случае - не выводим ничего, т.е. null.

Чтобы взять вышестоящую ячейку с фамилией, мы сначала ссылаемся на таблицу с предыдущего шага #"Добавлен индекс", а затем указываем имя нужного нам столбца [Column1] в квадратных скобках и номер ячейки в этом столбце в фигурных. Номер ячейки будет на единицу меньше, чем текущий, который мы берём из столбца Индекс, соответственно.

3. Останется заполнить пустые ячейки с null именами из вышестоящих ячеек командой Преобразование - Заполнить - Вниз (Transform - Fill - Down) и удалить ненужный более столбец с индексами и строки с фамилиями в первом столбце. В итоге получим:

Добавленный столбец с менеджерами

Шаг 4. Группируем в отдельные таблицы по менеджерам

Следующим шагом сгруппируем строки по каждому менеджеру в отдельные таблицы. Для этого на вкладке Преобразование используем команду Группировать по (Transform - Group By) и в открывшемся окне выберем столбец Менеджер и операцию Все строки (All rows), чтобы просто собрать данные, не применяя к ним никакой агрегирующей функции (суммы, среднего и т.п.):

Группируем строки в таблицы по менеджерам

В итоге получим отдельные таблицы для каждого менеджера:

Свернутые таблицы

Шаг 5. Преобразуем вложенные таблицы

Теперь приведём таблицы, лежащие в каждой ячейке получившегося столбца ВсеДанные в приличный вид.

Сначала удалим в каждой таблице ненужный более столбец Менеджер. Опять используем Настраиваемый столбец на вкладке Преобразование (Transform - Custom column) и следующую формулу:

Удаляем столбец Менеджер

Затем ещё одним вычисляемым столбцом поднимаем в каждой таблице первую строку в заголовки:

Поднимаем заголовки

И наконец выполняем главное преобразование - отмену свёртывания каждой таблицы с помощью М-функции Table.UnpivotOtherColumns:

Отмена свертывания

Названия регионов из шапки уйдут в новый столбец и мы получим на выходе более узкую, но при этом более длинную таблицу нормализованного вида. Пустые ячейки с null при этом игнорируются.

Избавившись от ненужных промежуточных столбцов, имеем:

Нормализованные таблицы

Шаг 6. Разворачиваем вложенные таблицы

Останется развернуть все нормализованные вложенные таблицы в единый список, используя кнопку с двойными стрелками в шапке столбца:

Разворачиваем вложенные таблицы

... и мы, наконец, получим то, что требовалось:

Итоговый результат

Выгрузить результирующую табличку обратно в Excel можно с помощью команды Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...).

Ссылки по теме




26.01.2022 21:49:53
Уважаемый Николай!
У меня все получилось )))
Большое спасибо за Ваши праведные труды по образовыванию глубинного темного населения типа меня.
Низкий поклон и большой респект ВАМ!

Опять же у меня есть пожелание на будущее.
Мое субъективное мнение - усложнять примеры надо крайне осторожно дабы "не сорвать резьбу".
Анпивот вложенных таблиц лучше было бы дать отдельным коротким примером так как в решении данной задачи он не был нужен но утяжелил ролик для понимания такого чайника как я (((.
Я большой сторонник копотких понятных примеров так как по моему мнению именно на таких примерах проще всего изучить предмет.
Разумеется, это исключительно мое субъективное.
Еще раз большое спасибо!!!
Наверх