Прочитать на английском

Поделиться через


Группирование или сводка строк

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

  • Группировки столбцов.

  • Группировки строк.

В этом руководстве вы используете следующую примерную таблицу.

Пример начальной таблицы.

Снимок экрана: таблица со столбцами, показывающими Год (2020), Страна (США, Панама или Канада), Продукт (рубашка или шорты), Канал продаж (онлайн или торговый посредник) и Единицы (различные значения от 55 до 7500)

Где найти группу по кнопке

Группу можно найти в трех местах:

  • На вкладке "Главная " в группе "Преобразование ".

    Снимок экрана: лента Power Query с группой по параметру, выделенная на вкладке

  • На вкладке "Преобразование" в группе таблиц.

    Снимок экрана: лента Power Query с группой по параметру, выделенная на вкладке

  • В контекстном меню при щелчке правой кнопкой мыши выберите столбцы.

    Снимок экрана: таблица с группой по параметру, выделенная в контекстном меню.

Использование статистической функции для группировки по одному или нескольким столбцам

В этом примере цель состоит в том, чтобы суммировать общее количество проданных единиц на уровне страны и канала продаж. Столбцы Country и Sales Channel используются для выполнения группы по операции.

  1. На вкладке "Главная" выберите группу.
  2. Выберите параметр "Дополнительно", чтобы выбрать несколько столбцов для группировки.
  3. Выберите столбец "Страна".
  4. Выберите " Добавить группирование".
  5. Выберите столбец "Канал продаж".
  6. В поле "Создать столбец" введите "Всего единиц" в операции, выберите "Сумма" и " Столбец" выберите "Единицы".
  7. Выберите ОК

Снимок экрана: диалоговое окно

Эта операция предоставляет следующую таблицу.

Снимок экрана: пример выходной таблицы со столбцами

Доступные операции

С помощью группы по функциям доступные операции можно классифицировать двумя способами:

  • Операция уровня строк
  • Операция уровня столбца

В следующей таблице описывается каждая из этих операций.

Имя операции Категория Description
Sum Операция столбца Суммирует все значения из столбца
Average Операция столбца Вычисляет среднее значение из столбца
Median Операция столбца Вычисляет медиану из столбца
Min Операция столбца Вычисляет минимальное значение из столбца
Max Операция столбца Вычисляет максимальное значение из столбца
Процентиль Операция столбца Вычисляет процентиль, используя входное значение от 0 до 100 из столбца.
Подсчет уникальных значений Операция столбца Вычисляет количество отдельных значений из столбца
Count : подсчет строк Операция строки Вычисляет общее количество строк из заданной группы
Подсчет отдельных строк Операция строки Вычисляет количество отдельных строк из заданной группы.
Все строки Операция строки Выводит все сгруппированные строки в табличном значении без агрегирования

Примечание

В Power Query Online доступны разные значения и операции процентиля .

Выполнение операции для группировки по одному или нескольким столбцам

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

Снимок экрана: пример выходной таблицы с операциями.

  1. Используйте следующие столбцы в качестве группы по столбцам :

    • Страна/регион
    • Канал продаж
  2. Создайте два новых столбца, выполнив следующие действия.

    1. Агрегирование столбца "Единицы" с помощью операции Sum . Присвойте этому столбцу общее число единиц.
    2. Добавьте новый столбец Products с помощью операции "Все строки ".

    Снимок экрана: диалоговое окно

После завершения операции обратите внимание, что столбец Products имеет значения [Table] внутри каждой ячейки. Каждое значение [Таблица] содержит все строки, сгруппированные по столбцам Country и Sales Channel из исходной таблицы. В ячейке можно выбрать пробел, чтобы просмотреть содержимое таблицы в нижней части диалогового окна.

Снимок экрана: панель предварительного просмотра сведений о таблице.

Примечание

Панель предварительного просмотра сведений может не отображать все строки, которые использовались для операции с группировкой. Вы можете выбрать значение [Таблица], чтобы просмотреть все строки, относящиеся к соответствующей операции по группе.

Затем необходимо извлечь строку, которая имеет наибольшее значение в столбце "Единицы" таблиц в новом столбце Products, и вызвать новый продукт верхнего исполнителя столбца.

Извлечение сведений о продукте верхнего исполнителя

Используя новый столбец Products со значениями [Таблица], создайте новый настраиваемый столбец, перейдя на вкладку "Добавить столбец " на ленте и выбрав настраиваемый столбец из группы "Общие ".

Снимок экрана: лента Power Query с параметром

Присвойте новому продукту верхнего исполнителя столбца. Введите формулу Table.Max([Products], "Units" ) в формуле настраиваемого столбца.

Снимок экрана: диалоговое окно

Результат этой формулы создает новый столбец со значениями [Record]. Эти значения записей по сути являются таблицей с одной строкой. Эти записи содержат строку с максимальным значением столбца "Единицы " каждого значения [Таблица] в столбце Products .

Снимок экрана: результат пользовательской формулы столбца с table.Max.

С помощью этого нового столбца продукта верхнего исполнителя, содержащего значения [Запись], можно выбрать значок развертывания, выбрать поля "Продукт и единицы", а затем ОК.

Снимок экрана: операция развертывания для значения записи в столбце верхнего продукта исполнителя.

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

Снимок экрана: окончательная таблица со всеми преобразованиями.

Нечеткое группирование

Примечание

Следующая функция доступна только в Power Query Online.

Чтобы продемонстрировать, как выполнить "нечеткое группирование", рассмотрим пример таблицы, показанной на следующем рисунке.

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

Цель нечеткой группировки — выполнить операцию по группе, которая использует приблизительный алгоритм сопоставления для текстовых строк. Power Query использует алгоритм сходства Jaccard для измерения сходства между парами экземпляров. Затем она применяет агломеративную иерархическую кластеризацию к экземплярам группирования. На следующем рисунке показаны ожидаемые выходные данные, в которых таблица сгруппирована по столбцу Person .

Снимок экрана: таблица с записями для Person в качестве Мигельа и Майка, а частота — 3 и 2 соответственно.

Чтобы выполнить нечеткое группирование, выполните те же действия, которые ранее описаны в этой статье. Единственное различие заключается в том, что на этот раз в диалоговом окне "Группа по " установите флажок "Использовать нечеткое группирование ".

Снимок экрана: флажок

Для каждой группы строк Power Query выбирает наиболее частый экземпляр в качестве канонического экземпляра. Если несколько экземпляров происходят с одной частотой, Power Query выбирает первый. После нажатия кнопки "ОК" в диалоговом окне "Группа по", вы получите ожидаемый результат.

Снимок экрана: пример конечной таблицы с нечеткой группировкой без преобразования.

Однако у вас больше контроля над нечеткой операцией группировки путем расширения параметров нечетких групп.

Снимок экрана: диалоговое окно

Для нечетких группирования доступны следующие параметры:

  • Порог сходства (необязательно) — этот параметр указывает, насколько похожи два значения должны быть сгруппированы. Минимальный параметр нуля (0) приводит к группировке всех значений. Максимальное значение 1 позволяет сгруппировать только значения, которые совпадают точно. Значение по умолчанию — 0.8.
  • Игнорировать случай: если сравниваются текстовые строки, регистр игнорируется. Этот параметр по умолчанию включен.
  • Группируйте путем объединения текстовых частей: алгоритм пытается объединить текстовые части (например, объединение Микро и обратимо в Корпорацию Майкрософт) для группирования значений.
  • Отображение показателей сходства: отображение показателей сходства между входными значениями и вычисляемых репрезентативных значений после нечетких группирования. Требуется добавление операции, например "Все строки" , чтобы продемонстрировать эти сведения на уровне строк.
  • Таблица преобразования (необязательно) — можно выбрать таблицу преобразования, которая сопоставляет значения (например , сопоставление MSFT с Корпорацией Майкрософт), чтобы сгруппировать их вместе.

В этом примере таблица преобразования используется для демонстрации того, как можно сопоставить значения. Таблица преобразования содержит два столбца:

  • Из: текстовая строка для поиска в таблице.
  • Чтобы: текстовая строка, используемая для замены текстовой строки в столбце From .

На следующем рисунке показана таблица преобразования, используемая в этом примере.

Снимок экрана: таблица со значениями майка и Уильяма, а также значения Miguel и Bill.

Важно!

Важно, чтобы в таблице преобразования были одинаковые столбцы и имена столбцов, как показано на предыдущем изображении (они должны быть помечены как "From" и "To"). В противном случае Power Query не распознает таблицу как таблицу преобразования.

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

Снимок экрана: раскрывающееся меню таблицы преобразования с нечеткой группировкой.

После выбора таблицы преобразования нажмите кнопку "ОК". Результат этой операции дает следующие сведения:

Снимок экрана: пример конечной таблицы с нечеткой группировкой с таблицей преобразования.

В этом примере параметр "Игнорировать регистр" включен, поэтому значения в столбце From таблицы преобразования используются для поиска текстовой строки, не учитывая регистр строки. Эта операция преобразования выполняется сначала, а затем выполняется нечеткое группирование.

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

Примечание

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

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

См. также

Добавление настраиваемого столбца
Удаление дубликатов