Работа с электронными таблицами

Указываем папку, в которой находятся файлы

В окне предварительного просмотра нажимаем кнопку «Преобразовать данные»

В редакторе Power query устанавливаем фильтры, чтобы исключить лишние файлы в папке. Можно создать фильтр по столбцу «Extension» оставив только файлы с расширением «.xlsx». Либо по названию файла в столбце «Name». Например оставив только файлы, которые начинаются на «Планы»

Выделяем столбец «Name», переходим на вкладку «Преобразование», выбираем «Замена значений», сначала «Планы» на «ничего»

Затем «.xlsx» на «ничего»

Оставляем только нужные нам столбцы: «Content» – содержимое файлов, «Name» – где у нас содержится год

На столбце «Content» нажимаем стрелки в заголовке столбца, чтобы объединить файлы

В открывшемся меню «Объединить файлы» оставляем по умолчанию в качестве примера «Первый файл», а ниже выбираем не конкретный лист, а строку «Параметр1», нажимаем «ОК»

Кроме автоматически созданной функции, создаются и четыре шага
Удаляем три нижних шага, оставляя только шаг «Вызвать настраиваемую функцию1»

Переходим в автоматически созданную функцию в запрос «Преобразовать пример файла», выделяем столбцы с именем и содержимым листов: Name и Data

Удаляем другие столбцы

В столбце с данными в заголовке нажимаем стрелки и разворачиваем все столбцы. Рекомендую снять галочку «Использовать исходное имя столбца как префикс»

На вкладке «Преобразование» нажимаем кнопку «Использовать первую строку в качестве заголовков»

Удаляем автоматически созданный шаг «Измененный тип»

Удаляем столбец «Торговая марка» так как это атрибут товара и он есть у нас в справочнике товаров, а планы у нас по товарам.

Для столбца «Регион» используем функцию: Преобразование – Заполнить – Вниз. Тем самым восстанавливая недостающие значения. Применение данной функции возможно только, если строки равны null

Фильтруем столбец «Код товара» исключая строки со значением «NULL» - это строки подитогов по региону и «Код товара» - это строки таблиц с других листов.

Чтобы получить плоскую таблицу удобную для аналитики необходимо развернуть столбцы месяцев, для этого выделяем столбцы: Годовой, Регион, Код товара – нажимаем правую клавишу мыши по заголовку выделенных столбцов и выбираем «Отменить свертывание других столбцов» Тоже самое можно сделать на вкладке «Преобразование» – «Любой столбец» – «Отменить свертывание столбцов»

Удаляем из столбца «Атрибут» строки со значением «Общий итог»

Переходим в основной запрос и удаляем столбец «Content»

Разворачиваем столбец «Преобразовать файл»

Выделяем столбец «Name» и столбец «Атрибут» содержащий названия месяцев. Нажимаем правую клавишу мыши – Объединить столбцы

В появившемся меню задаем параметры объединения: Разделитель «—Пользовательский—» = «.» и задаем имя столбца, например «Month». Нажимаем «ОК»

Для столбца «Month» задаем тип данных «Дата»

Для столбца «Код товара» задаем тип данных «Целое число»

Для столбца «Значение» задаем тип данных «Десятичное число»

Переименовываем столбцы «Годовой», «Регион», «Код товара», «Значение»

Переименовываем столбцы «TypePlan», «RegionName», «SkuID», «Amount»