Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PQ группировка данных в длинной таблице
 
#1
07.02.2022 10:43:58
Добрый день!
В процессе обработки данных в Power Query на определенном шаге возникает необходимость сгруппировать данные, чтобы в одном столбце остались только уникальные значения, а во всех других столбцах рассчиталась сумма. Я использую функцию "Группировать по", затем Подробнее, а потом вручную добавляю для каждого столбца название, выбираю тип агрегирования (сумма) и столбец. Полученная сгруппированная таблица обрабатывается дальше в PQ.
Обрабатываемая таблица содержит до 20 столбцов в определенной последовательности и вся процедура длится некоторое время, потому что для каждого столбца нужно вписать название и т.д.
Подскажите, есть ли возможность как-то автоматизировать процесс? Пользуюсь PQ буквально второй раз в жизни, но понимаю, что должно быть более простое решение.
Во вложении пример таблицы, которую нужно сгруппировать.
 
 
 
#2
07.02.2022 10:50:44
так нужно?
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(Источник, {"месяц"}, "Атрибут", "Значение"),
    #"Сгруппированные строки" = Table.Group(#"Другие столбцы с отмененным свертыванием", {"месяц", "Атрибут"}, {{"сумма", each List.Sum([Значение]), type number}}),
    #"Сведенный столбец" = Table.Pivot(#"Сгруппированные строки", List.Distinct(#"Сгруппированные строки"[Атрибут]), "Атрибут", "сумма", List.Sum)
in
    #"Сведенный столбец"
 
 
 
#3
07.02.2022 11:15:07
Еще вариант без Unpivot-Pivot. Во втором шаге список наименований столбцов, по которым сумму не нужно вычислять.
Код
let
  src = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  notNeedArgegateClmnNames = { "месяц", "Еще какой-то столбец", "И еще..." },
  needAgregateClmnNames = List.Buffer ( List.RemoveItems ( Table.ColumnNames ( src ), notNeedArgegateClmnNames ) ),
  group = Table.Group (
    src,
    { "месяц" },
    List.Transform ( needAgregateClmnNames, ( x ) => { x, ( y ) => List.Sum ( Table.Column ( y, x ) ), type number } )
  )
in
  group
 
 
 
#4
07.02.2022 11:16:35
mechanix 85, да, спасибо, не догадался, что можно развернуть, обработать и потом опять свернуть таблицу.
Всё работает!

surkenny, тоже работает, хотя пришлось имя таблицы перебить, но тут уже как в том старом меме "ни...чего не понятно, но было очень интересно!". Мне пока ещё до такого уровня далеко, да и вариант с unpivot-pivot подходит полностью.
Изменено: Pesssimist - 07.02.2022 11:25:44
 
 
 
#5
07.02.2022 11:54:55
Цитата
Pesssimist написал:
вариант с unpivot-pivot подходит полностью
Используйте на здоровье :) Просто на больших объемах Pivot-Unpivot медленнее будет:

Если Вы вручную добавляете агрегирование, то код выглядит так:
Код
  group = Table.Group (
    src, 
    { "месяц" }, 
    {
      { "Столбец1", each List.Sum ( [Столбец1] ), type number }, 
      { "Столбец2", each List.Sum ( [Столбец2] ), type number }, 
      { "Столбец18", each List.Sum ( [Столбец18] ), type number }
    }
  )

Это можно переписать
Код
  group = Table.Group (
    src, 
    { "месяц" }, 
    {
      { "Столбец1", (y) => List.Sum ( Table.Column ( y, "Столбец1" ) ), type number }, 
      { "Столбец2", (y) => List.Sum ( Table.Column ( y, "Столбец2" ) ), type number }, 
      { "Столбец18", (y) => List.Sum ( Table.Column ( y, "Столбец18" ) ), type number } 
    }
  )

А кодом
Код
List.Transform ( needAgregateClmnNames, ( x ) => { x, ( y ) => List.Sum ( Table.Column ( y, x ) ), type number } )

Мы как раз и создаем такой список списков:
Код
    {
      { "Столбец1", (y) => List.Sum ( Table.Column ( y, "Столбец1" ) ), type number }, 
      { "Столбец2", (y) => List.Sum ( Table.Column ( y, "Столбец2" ) ), type number }, 
      { "Столбец18", (y) => List.Sum ( Table.Column ( y, "Столбец18" ) ), type number } 
    }
 
 
 
Страницы: 1
Наверх