Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.
Предыдущий раздел К содержанию Следующий раздел
Ранее мы рассмотрели отмену свертывания столбцов для простой сводной таблицы и иерархии 2х2. Сейчас мы обобщим метод на сводную таблицу с любым числом уровней иерархии в строках и столбцах.
Загрузите файл C07E01.xlsx. Откройте его. Исходная таблица напоминает сводную таблицу с полями Country, State/Region и City, которые находятся в области строк, а поля Color, Parent Category и Category расположены в области столбцов. Поле Revenue находится в области значений.
Рис. 1. Таблица с исходными данными напоминает сводную таблицу в Excel; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Скачать заметку в формате Word или pdf, примеры в формате архива (внутри несколько файлов Excel без поддержки макросов)
Хотя исходная таблица имеет уровни иерархии 3*3, ради обобщения показанных здесь шагов будем ссылаться на эту таблицу как N*M. Вот основные шаги, необходимые для выполнения преобразования отмены свертывания столбцов в любой обобщенной сводной таблице:
Рис. 2. Обобщенный алгоритм преобразования отмены свертывания столбцов
Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C07E01.xlsx и кликните Импорт. В окне Навигатор выберите таблицу Revenues и щелкните Преобразовать данные. Удалите автоматически созданные шаги Измененный тип и Повышенные заголовки.
Далее в алгоритме буква N обозначает количество полей строк; в данном случае N = 3. Буква M обозначает количество полей столбцов; в данном случае M = 3. Обратите внимание, что первые два столбца (N–1) включают нулевые значения (вследствие иерархической структуры полей строк).
Шаг 1. Выберите первые N–1 столбцов и пройдите Преобразование –> Заполнить –> Вниз.
Шаг 2. Выделите первые N столбцов и пройдите Преобразование –> Объединить столбцы. В окне Объединить столбцы выберите Разделитель – Двоеточие, щелкните Ok.
Убедитесь, что выбран разделитель, который отсутствует в наборе исходных данных. В качестве разделителя вместо двоеточия можно выбрать редкую комбинацию символов.
Шаг 3. Пройдите Преобразование –> Транспонировать.
Шаг 4. Выберите первые M–1 столбцов и пройдите Преобразование –> Заполнить –> Вниз
Шаг 5. Пройдите Преобразование –> Использовать первую строку в качестве заголовка.
Шаг 6. Выберите первые M столбцов. Пройдите Преобразование –> Отменить свертывание столбцов –> Отменить свертывание других столбцов. Теперь столбец Атрибут содержит объединенные комбинации значений полей строк (например, United Kingdom:England:Liverpool), которые были объединены на шаге 2.
Шаг 7. Выберите столбец Атрибут, пройдите Преобразование –> Разделить столбец –> По разделителю –> Двоеточие, щелкните Ok.
Шаг 8. Переименуйте первые M столбцов: Color, Parent Category и Category. Переименуйте следующие N столбцов: Country, State/Region и City. Переименуйте столбец Значение в Revenue и измените тип столбца на Десятичное число или Валюта.
Загрузите запрос в таблицу на лист Excel.
Рис. 3. Идеально подготовленная таблица фактов