Как из сводной таблицы сделать плоскую в Power Query
Когда мы получаем данные из выгрузки или от коллег, часто возникает проблема со структурой таблицы. Встаёт вопрос: «Как привести данные к нужной структуре, чтоб построить удобный и простой отчёт?».
Рассмотрим как должна выглядеть правильная структура таблицы. Правила будут следующими:
У каждого столбца должен быть заголовок.
В каждом столбце данные должны быть однородные, т.е. одного типа. Например, если столбец несет под собой значения даты, то в каждой строке в столбце «Дата» должен быть единый тип.
Рассмотрим преобразования трёх самых популярных типов сводной таблицы.
1. В заголовках имеем диапазон по дате или другим категориям
Чтобы исправить такую таблицу необходимо:
через CTRL выделить все столбцы с диапазоном, в данном случае кварталы;
перейти во вкладку «Преобразование»;
найти кнопку «Отменить свертывания столбцов».
Получаем таблицу, с которой можем дальше проводить анализ в Power BI: 2. В одном столбце неоднородные данные
Бывает, что столбец с названием показателя вынесен отдельно: Выделим нужный столбец и нажмем на кнопку «Столбец сведения», после чего откроется меню настройки. Во вкладке «Столбец значений» выбираем значения, которые попадут в новые столбцы. Во вкладке «Функция агрегированного значения» выбираем пункт «Не агрегировать».
По окончании проделанных шагов получаем таблицу на рисунке ниже: 3. Сложная комбинация пунктов 1 и 2.
При комбинации случаев 1 и 2 первым делом необходимо избавиться от пустых значений null. Для этого выберем первый столбец и нажмем «Заполнить значения вниз».
Пустые значения первого столбца пропали, а на их месте теперь название филиала, которое было выше. Далее перевернем таблицу, для этого нажмем на кнопку «Транспонировать», после выбираем «Заполнить значения вниз», как указано на рисунке ниже:
Следующим шагом избавимся от нескольких заголовков. Для этого необходимо объединить столбцы и нажать на кнопку «Объединить столбцы»:
Столбцы склеиваются в один:
Транспонируем таблицу обратно и используем первую строку в качестве заголовка:
Далее действуем как в предыдущих примерах. Выделяем нужный диапазон и нажимаем «Отменить свертывание столбцов»:
Разделим ранее склеенный столбец, чтобы отделить год. Выделим столбец, где указаны кол-во и сумма, далее нажмем столбец сведения:
В результате получим простую таблицу, с которой удобно работать и которую легко анализировать: