Группировка в Power Query - ключевой шаг в любом алгоритме. Часто группировка меняет как количество строк, так и количество столбцов.
Сегодня разберём все типы группировки на примерах.
Данные можно агрегировать разными способами:
- группировать и суммировать данные,
- группировать и подсчитывать количество строк,
- группировать и находить среднее значение,
- группировать и находить максимальное/минимальное значение,
- группировать и находить количество уникальных строк,
- группировать "все строки" во вложенную таблицу.
Рассмотрим на примерах разные возможности группировки данных.
Группировка с суммой
Пример: сгруппировать выручку по офисам продаж.
Шаг1: Добавить таблицы в Power Query
Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы / диапазона. Для новых версий Excel: вкладка Данные → Из таблицы / диапазона.
Шаг2: Группируем и суммируем данные
Выберите в таблице столбец, по которому будем группировать данные. В нашем примере это столбец «Офис продаж». В редакторе Power Query на вкладке "Главная" выбираем "Группировать по":
В диалоговом окне нужно определить столбец, задать имя нового столбца, способ вычисления и столбец для вычисления.
Power Query по умолчанию предлагает для группировки ранее выбранный нами столбец «Офис продаж». Задаем новое имя «Сумма выручки», из списка операций выбираем способ вычисления «Сумма» и указываем по какому столбцу делать суммирование, в нашем примере это «Выручка».
На выходе получаем выручку, сгруппированную по офисам продаж.
В качестве дополнительного бонуса функция "Группировать по" удаляет из таблицы все столбцы, которые не были указаны в области "Группировка".
Группировка по нескольким столбцам
Группировку в Power Query можно делать по нескольким столбцам. Пример: посчитать количество сделок по каналам продаж с разбивкой по городам.
После загрузки таблицы в Power Query, вызываем команду "Группировать по" на вкладке Главная.
Анонсы всех видео, статей и полезностей - в нашем Telegram🔥
Присоединяйтесь, обсуждайте и автоматизируйте!
В открывшемся диалоговом окне нам нужно поставить точку напротив «Подробнее», чтобы мы смогли сделать группировку по нескольким столбцам и задать расширенные параметры. Чтобы добавить дополнительные столбцы для группировки, кликаем на кнопку «Добавить группирования»:
После этого задаем новое имя «Количество сделок», из списка операций выбираем способ вычисления «Считать строки». При операции «Считать строки» по какому столбцу делать вычисление указывать не нужно.
В результате получаем таблицу, сгруппированную по двум столбцам.
Группировка "Все строки" (All Rows)
Функция группировки скрывает еще одну неочевидную возможность: выводить все сгруппированные строки в виде таблицы без агрегирования.
Пример: получить данные по лучшим результатам продажам в разрезе по городам и по каналам продаж.
После загрузки таблицы в Power Query, на вкладке "Главная" вызываем команду "Группировать по".
В открывшемся диалоговом окне нам нужно поставить точку напротив «Подробнее». Кликаем на кнопку «Добавить группирования» и добавляем дополнительный столбец «Канал продаж»:
После этого задаем новое имя «Продажи», из списка операций выбираем способ вычисления «Все строки»:
В таблице появился новый столбец – Продажи. Он весьма необычен. Каждая Table содержит все строки, которые были сгруппированы по столбцам Город и Канал продаж из вашей исходной таблицы. Чтобы увидеть это, кликните пробел справа от слова Table. Внизу появится поле с новой таблицей. Таблица внизу окна показывает какие строки использовались для создания сгруппированных значений:
ВАЖНО: На панели предварительного просмотра сведений могут отображаться не все строки, которые использовались при группировке. Вы можете нажать на "Table", чтобы просмотреть все строки, относящиеся к соответствующей операции группировки.
Чтобы получить максимальное значение продаж, создадим пользовательский столбец с формулой. В редакторе Power Query перейдите на вкладку Добавление столбца –> Настраиваемый столбец.
В открывшемся окне задаем имя нового столбца «Максимальные продажи», и вводим функцию Table.Max:
Используемая формула проверяет таблицу и извлекает максимальное значение. Поиск ведется в столбце Продажи, поскольку он содержит таблицы, которые необходимо проверить. Максимум берется по столбцу «Продажи, тыс. руб» таблицы.
Однако, функция Table.Max возвращает не значение, а запись. Эта запись содержит не только максимальное значение, но и все детали.
Столбец «Максимальные продажи» можно развернуть с помощью кнопки 
с двуглавой стрелкой. Выберите нужные столбцы для финальной таблицы, отключите «Использовать исходное имя столбца как префикс». Нажмите OK.
Щелкните правой кнопкой мыши по столбцу Продажи - Удалить.
Мы получим финальную таблицу с максимальными продажами по каждому каналу продаж с детализацией по городам.
Мы специально использовали группировку "Все стоки" (All Rows), хотя для расчёта максимума можно было обойтись и без неё. Я не рекомендую злоупотреблять фукнцией "Все строки", потому что она может излишне загружать память компьютера. В нашем случае было бы проще сразу сделать группировку с вычислением максимальных значений, не используя All Rows. Именно так мы и рекомендуем делать, чтобы ваш алгоритм работал быстрее.
Как работает функция Table.Group
Что происходит с нашими таблицами при группировке? Для этого разберем функцию, с помощью которой и осуществляется группировка данных - Table.Group. На простом примере разберем её аргументы:
Первый аргумент – предыдущий шаг. В нашей формуле это - #"Измененный тип". В качестве источника используем таблицу из предыдущего шага.
Второй аргумент – список столбцов параметров, по которым мы делаем группировку. В нашей формуле это - {"Офис продаж"}.
Третий аргумент – список списков. В нашем примере один список - {{"Сумма выручки", each List.Sum([Выручка]), type nullable number}}.
"Сумма выручки" – название нового столбца показателя.
List.Sum – функция для суммирования значений (может быть выбрана любая другая функция вычислений).
[Выручка] – название столбца, к которому применяется расчет.
type nullable number – формат нового столбца, в нашем примере формат числовой.
Варианты группировки в Power Query
На примерах выше мы рассмотрели не все возможности группировки. Операций, которые можно применить при группировке, существенно больше.
В таблице ниже описана каждая из этих операций:
Название операции | Категория | Описание |
|---|---|---|
| Сумма | Работа с столбцами | Суммирует все значения из столбца |
| Среднее | Работа с столбцами | Вычисляет среднее значение из столбца |
| Медиана | Работа с столбцами | Вычисляет медиану из столбца |
| Мин. | Работа с столбцами | Вычисляет минимальное значение из столбца |
| Макс . | Работа с столбцами | Вычисляет максимальное значение из столбца |
| Считать строки | Операция со строкой | Вычисляет общее количество строк из заданной группы |
| Количество уникальных строк | Операция со строкой | Вычисляет количество уникальных строк из заданной группы |
| Все строки | Операция со строкой | Выводит все сгруппированные строки в табличное значение без агрегирования |
У Power Query большие возможности группировки, но иногда требуются и ручные доработки кода. Лично мне часто нужна группировка для получения списка. Например, мы хотим получить список товаров для каждого отдела продаж.
Возьмём формулу для суммирования выручки:
{{"Сумма выручки", each List.Sum([Выручка]), type nullable number}} Убираем функцию суммы, скобки и тип. Меняем названия столбцов и вот наша группировка выдаёт желаемый список товаров:
{{"Список товаров", each [Товар]}} Теперь мы получаем не сумму выручки, а список товаров в ячейке напротив каждого отдела продаж.
Заключение
При подготовке сводной таблицы в Power Query вам будет полезна функция группировки. В большом массиве повторяющихся данных важно все сгруппировать и получить все значения в рамках одной строки. Power Query имеет почти неограниченные возможности группировки. В особенных случаях вы можете немного доработать код группировки и добавить необходимые вычисления.
Будьте осторожны в применении группировки "Все строки" (All Rows). Данный тип группировки может привести к проблемам загрузки или обновления отчёта, так как сильно нагружает Power Query, особенно при больших таблицах. Лучше использовать его с осторожностью или обходиться другими методами.
Комментарии