Страницы: 1
RSS
PQ. Шаг с группировкой очень долго обрабатывается.
 
Друзья, привет!
Суть задачи, которую я пытаюсь решить - есть недельные данные, которые мне нужно свести по месяцам.
Алгоритм для этой цели придумал, создал запрос "НеделиЧастиМесяца", там я высчитываю долю каждой недели в прошлом и текущем месяце, ну а потом, нужно просто объединить Основную таблицу с долями недели и перемножить, данная часть работает - все отлично.
НО, на этапе, когда мне нужно сгруппировать полученные данные по месяцам (см. скриншот), происходит какой-то колапс, полное обновление PQ занимает около 40 минут, когда там максимум 16к строк.



Я не понимаю, почему группировка так крашит файл, может быть не в ней дело?
Так же у меня было предположение, что это из-за столбца с индексом, но без него обойтись я не смогу.
Надеюсь супер умы помогут советом.
Файл во вложении.
Изменено: OblivionR - 21.05.2020 18:09:17
 
Ваша проблема в этом шаге:
Код
= Table.AddColumn(
    #"Добавлен индекс", 
    "Новый месяц", 
    each try if [Атрибут.1] = "% Дней прошлого месяца" 
        then [Месяц] 
        else#"Добавлен индекс"[Месяц]{[Индекс]+1} 
    otherwise [Месяц])

Нельзя так ссылаться на строку ниже. Поищите в моих постах я предлагал здесь универсальную быструю функцию как добавлять к таблице столбцы со смещением. Щас некогда, Если не найдете. Позже покажу. Или еще кто поможет.
Вот горшок пустой, он предмет простой...
 
Такой вариант
Код
let
    Источник = #"Товар 6",
    #"Добавленный запрос" = Table.Combine({Источник, #"Товар 1", #"Товар 2", #"Товар 3", #"Товар 4", #"Товар 5"}),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Добавленный запрос", {"Год", "Артикул", "Отдел", "Канал", "Вывеска", "Вывеска ур 2"}, "Атрибут", "Значение"),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Другие столбцы с отмененным свертыванием",{"Год", "Атрибут", "Артикул", "Отдел", "Канал", "Вывеска", "Вывеска ур 2", "Значение"}),
    #"Сгруппированные строки" = Table.Group(#"Переупорядоченные столбцы", {"Год", "Атрибут", "Артикул", "Отдел", "Канал", "Вывеска"}, {{"Количество", each List.Sum([Значение]), type number}}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Сгруппированные строки",{{"Атрибут", Int64.Type}}),
    #"Объединенные запросы" = Table.NestedJoin(#"Измененный тип",{"Атрибут", "Год"},НеделиЧастиМесяца,{"Номера недель", "Год"},"НеделиЧастиМесяца",JoinKind.LeftOuter),
    #"Развернутый элемент НеделиЧастиМесяца" = Table.ExpandTableColumn(#"Объединенные запросы", "НеделиЧастиМесяца", {"Месяц", "% Дней прошлого месяца", "% Дней текущего месяца"}, {"Месяц", "% Дней прошлого месяца", "% Дней текущего месяца"}),
    #"Сортированные строки" = Table.Sort(#"Развернутый элемент НеделиЧастиМесяца",{{"Вывеска", Order.Descending}, {"Артикул", Order.Descending}, {"Атрибут", Order.Ascending}}),
    #"Несвернутые столбцы" = Table.UnpivotOtherColumns(#"Сортированные строки", {"Год", "Атрибут", "Артикул", "Отдел", "Канал", "Вывеска", "Количество", "Месяц"}, "Атрибут.1", "Значение"),
    Custom1 = Table.FromColumns(Table.ToColumns(#"Несвернутые столбцы")&{List.Skip(#"Несвернутые столбцы"[Месяц],1)},Table.ColumnNames(#"Несвернутые столбцы")&{"Месяц2"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(Custom1, "Новый месяц", each try if [Атрибут.1] = "% Дней прошлого месяца" then [Месяц] else
[Месяц2] otherwise [Месяц]),
    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Добавлен пользовательский объект", "План", each [Количество] * [Значение]),
    #"Измененный тип2" = Table.TransformColumnTypes(#"Добавлен пользовательский объект1",{{"План", type number}, {"Новый месяц", Int64.Type}}),
    Округлено = Table.TransformColumns(#"Измененный тип2",{{"План", each Number.Round(_, 0), type number}}),
    #"Другие удаленные столбцы" = Table.SelectColumns(Округлено,{"Год", "Артикул", "Отдел", "Канал", "Вывеска", "Новый месяц", "План"})
in
    #"Другие удаленные столбцы"
 
Цитата
PooHkrd написал:
Поищите в моих постах я предлагал здесь универсальную быструю функцию как добавлять к таблице столбцы со смещением
Этот же пост имеете в виду? Обязательно изучу.

Михаил Л, удивительно, как все начало летать, запрос грузиться за секунду. Спасибо!

Очень странно, что вариант со ссылкой на ячейку через инедкс дает большинство автор в интернете, ну по крайне мере в литературе, в которой я искал, без намека на производительность.

Еще раз всем спасибо.
 
Цитата
OblivionR написал:
запрос грузиться за секунду
Ничего себе! У меня на обновление таблицы надо 22 секунды  
 
OblivionR, этот пост. Михаил как раз дал вам решение по данному алгоритму. Вот его и изучайте.
А про большинство авторов скажу так: не все авторы одинаково полезны. По формированию столбца с накопительной суммой тоже это же большинство предлагает похожий подход, но более быстрого решения чем при помощи List.Generate я ещё не видел.
Изменено: PooHkrd - 22.05.2020 02:08:24
Вот горшок пустой, он предмет простой...
 
Цитата
Михаил Л написал:
надо 22 секунды
Проверял на домашнем компе, у меня на нем 20 гигов оперативки, на рабочем будет подольше, но в любом случае не 40 минут :D
 
OblivionR, при двух гигах на нетбуке -22 сек. Win7, Excel 2010
 
Кстати, заметил один небольшой "косяк", при подобном методе (смещение на одну строку ниже) последняя строка из добавляемой таблицы(столбца) имеет значение null
 
OblivionR, это не косяк, это так задумано, от списка же откусывается первый элемент, значит в конце будет на один элемент меньше. При этом никто абсолютно не мешает добавить к нему нужное вам значение в конце.
Изменено: PooHkrd - 22.05.2020 15:44:40
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх