Отмена свертывания столбцов таблицы в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

Отмена свертывания столбцов – основной метод при работе с неудовлетворительно спроектированными таблицами.

Идентификация плохо спроектированных таблиц

Если исходные таблицы хорошо составлены, то их можно эффективно использовать в сводных таблицах и сводных диаграммах. Неудовлетворительное оформление таблицы усложняет анализ данных. Далее будет показано, как можно реструктурировать плохо спроектированные таблицы. Один из способов получить представление о том, хорошо ли структурирована таблица, — посмотреть, достаточно ли верно каждая строка таблицы представляет реальный предмет. Таблица хорошо спроектирована, если каждая ее строка представляет уникальный объект.

Давайте рассмотрим таблицы доходов компании AdventureWorks.

Рис. 1. Таблицы доходов компании AdventureWorks; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате архива (внутри несколько файлов Excel без поддержки макросов)

Каждая таблица имеет различную схему для отображения общего дохода по счету клиента и родительской категории товара. Таблица 1 структурирована как типичная сводная таблица Excel. Учетная запись задается как поле строки, родительская категория – как поле столбца, а поле Revenues находится в области значений сводной таблицы. И хотя таблица 1 может неплохо выглядеть в окончательном отчете, она неэффективна, если используется в качестве источника данных.

Таблица 2 также структурирована в виде сводной таблицы. Поля Account и Parent Category задаются как поля столбцов, а Revenues – как значения сводной таблицы. Если вы знакомы со сводными таблицами, то наверняка знаете, что и таблица 1, и таблица 2 могут быть представлением одной и той же исходной таблицы.

Таблица 3 иллюстрирует крайний случай, когда структура таблицы превращается в общий контейнер пар «атрибут/значение». Несмотря на то, что эта таблица достаточно понятна для просмотра и определения контекста отдельных значений, она не может использоваться в качестве источника данных.

Таблица 4 обладает наилучшим дизайном среди представленных таблиц. Она представляет доходы компании AdventureWorks по учетным записям и родительской категории в удобном виде, который хорошо подходит для последующего анализа сводными таблицами. При выборе таблицы 4 в качестве источника данных для сводной таблицы в Excel можно легко создать и таблицу 1, и таблицу 2. Таблицы, структурированные как таблица 4, иногда называют таблицами фактов. В нашем случае фактами служат общие доходы (revenues), суммированные по уровням позиций (accounts) и родительских категорий (parent category).

Таблицы фактов часто рассматриваются вместе с таблицами поиска и с применением звездообразной схемы. Больше о таблице фактов в контексте модели данных см. Альберто Феррари, Марко Руссо. Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel.

Из вариантов, приведенных на рис. 1, таблица 4 является наилучшим примером представления исходных данных. Если же исходная таблица отформатирована по типу таблиц 1–3, Power Query позволит представить ее в виде идеальной таблицы фактов.

Знакомство с отменой свертывания столбцов

На вкладке Преобразование имеются три команды Отметить свертывание столбцов:

• Отменить свертывание столбцов
• Отменить свертывание других столбцов
• Отменить свертывание только выделенных столбцов

Power Query реструктурирует таблицу, создавая отдельную строку для каждой ячейки в столбцах, для которых отменено свертывание. Строка каждой такой ячейки включает столбцы, для которых не было отменено свертывание, а также два дополнительных столбца: Атрибут и Значение. Столбец Атрибут содержит исходное имя столбца ячейки, а столбец Значение содержит исходное значение.

Рис. 2. Действие преобразования Отмена свертывания столбцов

При использовании преобразования в таблице рассматривается два типа столбцов:

• Столбцы привязки – это столбцы, к которым не применяется преобразование отмены свертывания (эти столбцы не свертываются). Они представляют атрибуты, которые желательно сохранить. На рис. 2 единственным столбцом привязки является столбец Account. Но столбцов привязки может быть и несколько.
• Столбцы, для которых отменено свертывание, – это столбцы, для которых имеются разные проявления одного и того же атрибута. Например, столбцы Accessories, Bikes, Components и Clothing относятся к категории доходов. И хотя для каждого из этих столбцов родительская категория различна, все значения имеют один атрибут – Revenues.

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

Отмена свертывания столбцов и отмена свертывания других столбцов

Загрузите файл C06E01.xlsx. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C06E01.xlsx и кликните Импорт. В окне Навигатор выберите таблицу Revenues и щелкните Преобразовать данные. В окне редактора Power Query обратите внимание, что панель просмотра включает доходы компании AdventureWorks по столбцам Account, Accessories, Bikes, Clothing и Components. Выберите последние четыре столбца, например, выбрав столбец Accessories, а затем нажав Shift+End.

Пройдите Преобразование –> Отменить свертывание столбцов. Также можно щелкнут правой кнопкой мыши на одном из выбранных столбцов и выбрать Отменить свертывание столбцов. В строке формул можно увидеть:

Чтобы попрактиковаться удалить шаг Несвернутые столбцы. Выберите столбец Account. Пройдите Преобразование –> Отменить свертывание других столбцов. Результаты выполнения этой команды будут такими же, как и на предыдущем шаге. Более того, формула на языке М также не отличается. Единственное отличие будет в названии примененного шага. Теперь оно –  Другие столбцы с отмененным свертыванием.

Разницы в этих командах нет. Используйте ту из них, которая позволяет быстрее выделить столбцы. Если в будущем исходная таблица включит новые столбцы доходов с другими родительскими категориями, функция Table.UnpivotOtherColumns обеспечивает обновление без необходимости изменять запрос.

Распространенный сценарий, при котором функция Table.UnpivotOtherColumns приобретает большое значение, – если исходная таблица содержит несколько столбцов с одним и тем же атрибутом, относящимся к разным периодам времени, например к годам, кварталам или месяцам. В подобных случаях преобразование отмены свертывания столбцов делает запрос устойчивым и отменяет свертывание столбцов для новых периодов, что и требуется, а в последующие периоды времени можно добавлять новые столбцы без дальнейших изменений запроса.

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

Отменить свертывание только выделенных столбцов

Если ожидается, что исходная таблица в будущем будет иметь новые столбцы привязки, следует использовать команду Отменить свертывание только выделенных столбцов. Загрузите файл C06E02.xlsx. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C06E02.xlsx и кликните Импорт. В окне Навигатор выберите таблицу Revenues и щелкните Преобразовать данные. В окне редактора Power Query обратите внимание, что панель просмотра включает доходы компании Wide World Importer за 2015–2018 гг. Выберите последние четыре столбца. Пройдите Преобразование –> Отменить свертывание столбцов –> Отменить свертывание только выделенных столбцов. В таблице, как и ожидалось, отменено свертывание столбцов.

В строке формул:

Обратите внимание, что годы закодированы жестко. Если добавить 2019-й год, он будет обработан как столбец привязки, что приведет к ошибке. Этот метод следует использовать только, если ожидаются добавления новых столбцов привязки.

Обработка итогов

При отмене свертывания столбцов для сводных таблиц следует обращать внимание на итоговые столбцы и строки. Лучше удалять такие элементы на этапе импорта. Загрузите файл C06E03.xlsx. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C06E03.xlsx и кликните Импорт. В окне Навигатор выберите таблицу Revenues и щелкните Преобразовать данные. В окне редактора Power Query обратите внимание, что последняя строка и последний столбец содержат итоговые значения.

Выберите столбец Account и пройдите Преобразование –> Отменить свертывание столбцов –> Отменить свертывание других столбцов. После этого в таблице будет отменено свертывание столбцов. Однако среди Атрибутов имеются строки Grand Total, а при прокрутке таблицы последние пять строк включают Grand Total в столбце Account.

Сейчас можно отфильтровать значения Grand Total, но этот подход неэффективен, поскольку снижает производительность и увеличивает время загрузки. Лучше удалить ненужные столбец и строку Grand Total перед отменой свертывания столбцов. Удалите шаг Другие столбцы с отмененным свертыванием. Удалите столбец Grand Total. Пройдите Главная –> Удалить строки –> Удалить нижние строки –> 1, щелкните Ok.

Выберите столбец Account и пройдите Преобразование –> Отменить свертывание столбцов –> Отменить свертывание других столбцов. После этого в таблице будет отменено свертывание столбцов. Переименуйте столбец Атрибут в Parent Category, а столбец Значение – в Revenue.

Применение преобразования отмены свертывания столбцов на уровнях иерархии 2×2

В реальной жизни приходится сталкиваться с более сложными таблицами, которые включают несколько уровней агрегирования в столбцах и строках. Рассмотрим пример. В компании Wide World Importers делают отчет с обобщенными сведениями о доходах по годам, месяцам, категориям поставщиков и поставщикам. Цель – получить таблицу фактов.

Рис. 3. Сводный отчет 2х2 и таблица фактов

Чтобы подготовить почву для применения преобразования отмены свертывания столбцов, следует выполнить ряд преобразований:

• Заполнить значениями столбец Year
• Объединить столбцы Year и Month,
• Транспонировать таблицу,
• Заполнить значениями столбец Supplier Category.

Загрузите файл C06E04.xlsx. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C06E04.xlsx и кликните Импорт. В окне Навигатор выберите таблицу Revenues и щелкните Преобразовать данные. Удалите столбец Grand Total, затем удалить нижнюю строку Grand Total.

Хочу обратить внимание на следующее. Исходный файл имеет структуру, как на рис. 4а:

Рис. 4. Тонкости оформления исходного файла: а) запись Grand Total в верхней ячейке, б) запись Grand Total в нижней ячейке

В этом случае удаление столбца Grand Total приводит к формуле:

Если же исходный файл будет оформлен, как на рис. 4б, то формула, соответствующая удалению столбца Grand Total будет следующей:

В этом случае при добавлении новых столбцов в будущем Grand Total не будут соответствовать столбцу Column10, и скрипт потерпит неудачу. Вместо удаления столбца Grand Total по имени (всё равно какому: Grand Total или Column10), лучше удалить шаги Измененный тип и Повышенные заголовки, а затем удалить последний столбец с помощью формулы M:

Для этого щелкните f_х и введите формулу.

Выберите столбец Column1 и пройдите Преобразование –> Заполнить –> Вниз. Выделите столбцы Column1 и Column2 и пройдите Преобразование –> Объединить столбцы. В окне Объединить столбцы выберите Разделитель – пробел, щелкните Ok.

Пройдите Преобразование –> Транспонировать. Выделите первый столбец и пройдите Преобразование –> Заполнить –> Вниз, а затем Преобразование –> Использовать первую строку в качестве заголовка. Выберите первые два столбца (в данном случае это столбцы привязки, представляющие категорию поставщика и название поставщика). Пройдите Преобразование –> Отменить свертывание столбцов –> Отменить свертывание других столбцов.

Переименуйте столбцы: первый – Supplier Category, второй Supplier, третий – Date, четвертый – Revenue. Измените тип столбца Date на Дата. Удостоверьтесь, что для столбца Revenue выбран тип Десятичное число или Валюта. Загрузите запрос в таблицу на лист Excel.

Отмена свертывания столбцов для иерархии 2×2 без объединения иерархий

Выше мы объединили год и месяц. Рассмотрим случай, когда нельзя объединить двухуровневую иерархию строк:

Рис. 5. Пример отмены свертывания столбцов сложных таблиц

Загрузите файл C06E05.xlsx. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C06E05.xlsx и кликните Импорт. В окне Навигатор выберите таблицу Revenues и щелкните Преобразовать данные. Удалите шаги Измененный тип и Повышенные заголовки, а затем удалить последний столбец с помощью формулы M, как показано в предыдущем разделе:

Удалите последнюю строку Grand Total. Выделите столбец Column1, пройдите Преобразование –> Заполнить –> Вниз. Выделите столбцы Column1 и Column2, пройдите Преобразование –> Объединить столбцы. В окне Объединить столбцы выберите Разделитель – Двоеточие, щелкните Ok.

При предыдущем упражнении объединение столбцов было интуитивно понятным шагом, поскольку выполнено преобразование в единую дату. Сейчас объединение столбцов Sales Person и Company может показаться странным. Однако этот шаг важен для преобразования отмены свертывания столбцов. В дальнейшем столбцы будут вновь разделены. Почему при объединении столбцов было использовано двоеточие? Предполагается, что это значение отсутствует в объединяемых столбцах. Таким образом, можно будет правильно разделить столбцы позже. Для надежности в качестве разделителя можно выбрать иную редкую комбинацию символов, например, &@.

Пройдите Преобразование –> Транспонировать. Выберите столбец Column1, пройдите Преобразование –> Заполнить –> Вниз, а затем Преобразование –> Использовать первую строку в качестве заголовка. Выберите первые два столбца (которые являются столбцами привязки, представляющие категорию и подкатегорию товаров). Пройдите Преобразование –> Отменить свертывание столбцов –> Отменить свертывание других столбцов.

Выберите столбец Атрибут, пройдите Преобразование –> Разделить столбец –> По разделителю. В окне Разделить столбец по разделителю введите разделитель, выбранный выше (двоеточие или что-то более сложное), щелкните Ok.

Переименуйте столбцы: первый – Parent Category, второй – Category, третий – Sales Person, четвертый – Company, пятый – Revenue. Измените тип столбца Revenue на Десятичное число или Валюта. Загрузите запрос в таблицу на лист Excel.

Обработка промежуточных итогов

Промежуточные итоги также искажают данные, поэтому их желательно фильтровать на уровне запроса. Однако, это сделать сложнее, чем удаление общих итогов. Удалять промежуточные итоги удобнее после отмены свертывания столбцов, поскольку позиции столбцов для промежуточных итогов в исходной таблице могут измениться.

Загрузите файл C06E06.xlsx. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C06E06.xlsx и кликните Импорт. В окне Навигатор выберите таблицу Revenues и щелкните Преобразовать данные. Удалите шаги Измененный тип и Повышенные заголовки. Выделите столбец Column1, пройдите Преобразование –> Заполнить –> Вниз. Выделите столбцы Column1 и Column2, пройдите Преобразование –> Объединить столбцы. В окне Объединить столбцы выберите Разделитель – Двоеточие, щелкните Ok.

Пройдите Преобразование –> Транспонировать. Выберите столбец Column1, пройдите Преобразование –> Заполнить –> Вниз, а затем Преобразование –> Использовать первую строку в качестве заголовка. Выберите первые два столбца (которые являются столбцами привязки, представляющие категорию и подкатегорию товаров). Пройдите Преобразование –> Отменить свертывание столбцов –> Отменить свертывание других столбцов. Выберите столбец Атрибут, пройдите Преобразование –> Разделить столбец –> По разделителю. В окне Разделить столбец по разделителю введите разделитель Двоеточие, щелкните Ok.

Переименуйте столбцы: первый – Parent Category, второй – Category, третий – Sales Person, четвертый – Company, пятый – Revenue. Измените тип столбца Revenue на Десятичное число или Валюта.

Обратите внимание, что столбец Parent Category включает промежуточные итоги под заголовками Accessories Total, Bikes Total и т.д. Следует отфильтровать их. Выберите элемент управления фильтром в заголовке столбца Parent Category. Может возникнуть соблазн применить панель Фильтр и снять галочки с промежуточных итогов. Но если так поступить, то запрос отфильтрует только текущие надписи и пропустит в будущем новые надписи при добавлении в исходную таблицу новых родительских категорий.

Для корректного выполнения фильтрации выберите Текстовые фильтры –> Не заканчивается на –> Total, щелкните Ok. Можно проверить шаг фильтрации, щелкая на элементе управления фильтром в заголовке столбца Parent Category и убеждаясь, что все значения Total, включая Grand Total, теперь отсутствуют.

Выберите элемент управления фильтром в заголовке столбца Sales Person. Выберите Текстовые фильтры –> Не заканчивается на –> Total, щелкните Ok. Можно проверить шаг фильтрации, снова щелкая на элемент управления фильтром в заголовке столбца Sales Person и убедившись, что все значения итогов теперь отсутствуют.

Загрузите запрос в таблицу на лист Excel. Никаких общих и промежуточных итогов нет.

Рис. 6. Корректная таблица фактов