Группирование или сводка строк
В Power Query можно группировать значения в разных строках в одно значение, группируя строки в соответствии со значениями в одном или нескольких столбцах. Вы можете выбрать один из двух типов операций группирования:
Группировки столбцов.
Группировки строк.
В этом руководстве вы используете следующую примерную таблицу.
Снимок экрана: таблица со столбцами, показывающими Год (2020), Страна (США, Панама или Канада), Продукт (рубашка или шорты), Канал продаж (онлайн или торговый посредник) и Единицы (различные значения от 55 до 7500)
Где найти группу по кнопке
Группу можно найти в трех местах:
На вкладке "Главная " в группе "Преобразование ".
На вкладке "Преобразование" в группе таблиц.
В контекстном меню при щелчке правой кнопкой мыши выберите столбцы.
Использование статистической функции для группировки по одному или нескольким столбцам
В этом примере цель состоит в том, чтобы суммировать общее количество проданных единиц на уровне страны и канала продаж. Столбцы Country и Sales Channel используются для выполнения группы по операции.
- На вкладке "Главная" выберите группу.
- Выберите параметр "Дополнительно", чтобы выбрать несколько столбцов для группировки.
- Выберите столбец "Страна".
- Выберите " Добавить группирование".
- Выберите столбец "Канал продаж".
- В поле "Создать столбец" введите "Всего единиц" в операции, выберите "Сумма" и " Столбец" выберите "Единицы".
- Выберите ОК
Эта операция предоставляет следующую таблицу.
Доступные операции
С помощью группы по функциям доступные операции можно классифицировать двумя способами:
- Операция уровня строк
- Операция уровня столбца
В следующей таблице описывается каждая из этих операций.
| Имя операции | Категория | Description |
|---|---|---|
| Sum | Операция столбца | Суммирует все значения из столбца |
| Average | Операция столбца | Вычисляет среднее значение из столбца |
| Median | Операция столбца | Вычисляет медиану из столбца |
| Min | Операция столбца | Вычисляет минимальное значение из столбца |
| Max | Операция столбца | Вычисляет максимальное значение из столбца |
| Процентиль | Операция столбца | Вычисляет процентиль, используя входное значение от 0 до 100 из столбца. |
| Подсчет уникальных значений | Операция столбца | Вычисляет количество отдельных значений из столбца |
| Count : подсчет строк | Операция строки | Вычисляет общее количество строк из заданной группы |
| Подсчет отдельных строк | Операция строки | Вычисляет количество отдельных строк из заданной группы. |
| Все строки | Операция строки | Выводит все сгруппированные строки в табличном значении без агрегирования |
Примечание.
В Power Query Online доступны разные значения и операции процентиля .
Выполнение операции для группировки по одному или нескольким столбцам
Начиная с исходного примера, в этом примере создается столбец с общими единицами и двумя другими столбцами, которые дают вам имя и единицы, проданные для наиболее производительного продукта, суммируются на уровне страны и канала продаж.
Используйте следующие столбцы в качестве группы по столбцам :
- Страна/регион
- Канал продаж
Создайте два новых столбца, выполнив следующие действия.
- Агрегирование столбца "Единицы" с помощью операции Sum . Присвойте этому столбцу общее число единиц.
- Добавьте новый столбец Products с помощью операции "Все строки ".
После завершения операции обратите внимание, что столбец Products имеет значения [Table] внутри каждой ячейки. Каждое значение [Таблица] содержит все строки, сгруппированные по столбцам Country и Sales Channel из исходной таблицы. В ячейке можно выбрать пробел, чтобы просмотреть содержимое таблицы в нижней части диалогового окна.
Примечание.
Панель предварительного просмотра сведений может не отображать все строки, которые использовались для операции с группировкой. Вы можете выбрать значение [Таблица], чтобы просмотреть все строки, относящиеся к соответствующей операции по группе.
Затем необходимо извлечь строку, которая имеет наибольшее значение в столбце "Единицы" таблиц в новом столбце Products, и вызвать новый продукт верхнего исполнителя столбца.
Извлечение сведений о продукте верхнего исполнителя
Используя новый столбец Products со значениями [Таблица], создайте новый настраиваемый столбец, перейдя на вкладку "Добавить столбец " на ленте и выбрав настраиваемый столбец из группы "Общие ".
Присвойте новому продукту верхнего исполнителя столбца. Введите формулу Table.Max([Products], "Units" ) в формуле настраиваемого столбца.
Результат этой формулы создает новый столбец со значениями [Record]. Эти значения записей по сути являются таблицей с одной строкой. Эти записи содержат строку с максимальным значением столбца "Единицы " каждого значения [Таблица] в столбце Products .
С помощью этого нового столбца продукта верхнего исполнителя, содержащего значения [Запись], можно выбрать
значок развертывания, выбрать поля "Продукт и единицы", а затем ОК.
После удаления столбца Products и настройки типа данных для обоих только что развернутых столбцов результат будет выглядеть следующим образом.
Нечеткое группирование
Примечание.
Следующая функция доступна только в Power Query Online.
Чтобы продемонстрировать, как выполнить "нечеткое группирование", рассмотрим пример таблицы, показанной на следующем рисунке.
Цель нечеткой группировки — выполнить операцию по группе, которая использует приблизительный алгоритм сопоставления для текстовых строк. Power Query использует алгоритм сходства Jaccard для измерения сходства между парами экземпляров. Затем она применяет агломеративную иерархическую кластеризацию к экземплярам группирования. На следующем рисунке показаны ожидаемые выходные данные, в которых таблица сгруппирована по столбцу Person .
Чтобы выполнить нечеткое группирование, выполните те же действия, которые ранее описаны в этой статье. Единственное различие заключается в том, что на этот раз в диалоговом окне "Группа по " установите флажок "Использовать нечеткое группирование ".
Для каждой группы строк Power Query выбирает наиболее частый экземпляр в качестве канонического экземпляра. Если несколько экземпляров происходят с одной частотой, Power Query выбирает первый. После нажатия кнопки "ОК" в диалоговом окне "Группа по", вы получите ожидаемый результат.
Однако у вас больше контроля над нечеткой операцией группировки путем расширения параметров нечетких групп.
Для нечетких группирования доступны следующие параметры:
- Порог сходства (необязательно) — этот параметр указывает, насколько похожи два значения должны быть сгруппированы. Минимальный параметр нуля (0) приводит к группировке всех значений. Максимальное значение 1 позволяет сгруппировать только значения, которые совпадают точно. Значение по умолчанию — 0.8.
- Игнорировать случай: если сравниваются текстовые строки, регистр игнорируется. Этот параметр по умолчанию включен.
- Группируйте путем объединения текстовых частей: алгоритм пытается объединить текстовые части (например, объединение Микро и обратимо в Корпорацию Майкрософт) для группирования значений.
- Отображение показателей сходства: отображение показателей сходства между входными значениями и вычисляемых репрезентативных значений после нечетких группирования. Требуется добавление операции, например "Все строки" , чтобы продемонстрировать эти сведения на уровне строк.
- Таблица преобразования (необязательно) — можно выбрать таблицу преобразования, которая сопоставляет значения (например , сопоставление MSFT с Корпорацией Майкрософт), чтобы сгруппировать их вместе.
В этом примере таблица преобразования используется для демонстрации того, как можно сопоставить значения. Таблица преобразования содержит два столбца:
- Из: текстовая строка для поиска в таблице.
- Чтобы: текстовая строка, используемая для замены текстовой строки в столбце From .
На следующем рисунке показана таблица преобразования, используемая в этом примере.
Внимание
Важно, чтобы в таблице преобразования были одинаковые столбцы и имена столбцов, как показано на предыдущем изображении (они должны быть помечены как "From" и "To"). В противном случае Power Query не распознает таблицу как таблицу преобразования.
Вернитесь в группу по диалоговому окне, разверните параметры нечеткой группы, измените операцию с счетчиков на все строки, включите параметр "Показать оценки сходства", а затем выберите раскрывающееся меню таблицы преобразования.
После выбора таблицы преобразования нажмите кнопку "ОК". Результат этой операции дает следующие сведения:
В этом примере параметр "Игнорировать регистр" включен, поэтому значения в столбце From таблицы преобразования используются для поиска текстовой строки, не учитывая регистр строки. Эта операция преобразования выполняется сначала, а затем выполняется нечеткое группирование.
Оценка сходства также отображается в табличном значении рядом с столбцом person, который отражает точное группирование значений и их соответствующие оценки сходства. Этот столбец можно развернуть при необходимости или использовать значения из новых столбцов частоты для других типов преобразований.
Примечание.
При группировке по нескольким столбцам таблица преобразования выполняет операцию замены во всех столбцах, если при замене значения увеличивается оценка сходства.
Дополнительные сведения о работе таблиц преобразования см. в руководстве по таблицам преобразования.