Сводные столбцы

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

Схема, показывающая таблицу слева с пустым столбцом и строками. Столбец «Атрибуты» содержит девять строк, где A1, A2 и A3 повторяются по три раза. Столбец "Значения" содержит значения V1 по V9, сверху вниз. В результате поворота столбцов, таблица справа содержит пустой столбец и пустые строки, а также значения Атрибутов A1, A2 и A3 в виде заголовков столбцов. Столбец A1 содержит значения V1, V4 и V7, столбец A2 содержит значения V2, V5 и V8, а столбец A3 содержит значения V3, V6 и V9.

Представьте таблицу, как в следующем изображении.

Таблица, содержащая столбец "Страна" с типом данных "Текст", столбец "Дата" с типом данных "Дата", и столбец "Значение" с типом данных "Целое число". Столбец "Страна" содержит США в первых трех строках, Канаду в следующих трех строках и Панаму в последних трех строках. Столбец "Дата" содержит дату в первых, четвертых и седьмой строках, вторую дату во второй, пятой и восьмой строках, а третью — в третьей, шестой и девятой строках.

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

Таблица, содержащая столбец "Страна" в типе текстовых данных, а также первые, второй и третий столбцы дат, заданные в качестве типа данных "Целое число". Столбец "Страна" содержит Канаду в строке 1, Панама в строке 2 и США в строке 3.

Заметка

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

Повернуть столбец

1. Выберите столбец, который нужно повернуть. В этом примере выберите столбец date.

2. На вкладке Преобразование группы Любой столбец выберите Сводный столбец.

   

3. В диалоговом окне столбца сводной таблицы в списке столбца "Значение" выберите значение.

   

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

   

Доступны следующие варианты:

• Не агрегировать
• Считать (все)
• Число (не пустое)
• Минимум
• Максимум
• Медиана
• Сумма
• Средний

Сводные столбцы, которые нельзя агрегировать

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

Таблица со столбцом Country, содержащим США в первых трех строках, Канада в следующих трех строках и Панама в последних трех строках. Столбец "Позиция" содержит первое место в первой, четвертой и седьмой строках, второе место во втором, пятом и восьмом строках, а третий — в третьем, шестом и девятом строках.

Предположим, что вы хотите преобразовать столбец Position в этой таблице, чтобы вы могли иметь его значения в виде новых столбцов. Для значений этих новых столбцов используются значения из столбца Product. Выберите столбец Position, а затем выберите поворотный столбец , чтобы выполнить поворот этого столбца.

В диалоговом окне столбца сводной таблицы выберите столбец Product в качестве столбца значения. Нажмите кнопку Дополнительно в диалоговом окне Сводные столбцы, а затем выберите Не агрегировать.

Результат этой операции дает результат, показанный на следующем рисунке.

Таблица, содержащая столбцы Country, First Place, Second Place и Third Place, с столбцом Country, содержащим Канаду в строке 1, Панама в строке 2 и США в строке 3.

Ошибки при использовании параметра "Не агрегировать"

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

Таблица со столбцами "Страна", "Дата" и "Значение". Столбец Country содержит США в первых трех строках, Канаду в следующих трех строках и Панаму в последних трех строках. Столбец Date содержит одну дату во всех строках. Столбец значений содержит различные целые числа от 20 до 785.

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

Область редактора Power Query с таблицами со столбцами "Страна" и "Дата". Столбец "Страна" содержит Канаду в первой строке, Панама во второй строке и США в третьей строке. Все строки в столбце значений даты содержат ошибки, при этом выбрана одна ошибка. Под таблицей находится другая панель, где отображается ошибка выражения с сообщением "В перечислении слишком много элементов для завершения операции".

Обратите внимание на сообщение об ошибке "Expression.Error: Слишком много элементов в перечислении, чтобы завершить операцию". Эта ошибка возникает, потому что операция Не агрегировать ожидает только одно значение для комбинации страны и даты.