PowerBI / PowerQuery: ускорение медленного вызова настраиваемой функции (для расчета балансов)

Функция выполняет три операции сортировки и суммирует результат для каждой строки исходной таблицы (Movements).

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

Есть ли способ лучше?

SelectBalanceFromMovements (,,):

    (TrYrPr as number,
    ProdNo as number,
    StorageNo as number) =>
    let
        Source = Table.SelectRows(Movements, each [TransYearPeriod] < TrYrPr),
        Step1 = Table.SelectRows(Source, each [ProdNo] = ProdNo),
        Step2 = Table.SelectRows(Step1, each [StorageNumber] = StorageNo),
        Step3 = Table.Group(Step2, {"ProdNo", "StorageNumber"}, {{"Balance", each List.Sum([Movement]), type number}}),
        Step4 = Table.AddColumn(Step3, "TransYearPeriod", each TrYrPr)
    in
        Step4

Изменения:

Изменено предложением greggyb объединить шаги фильтра, и мне не нужен столбец периода:

(TrYrPr as number,
ProdNo as number,
StorageNo as number) =>
let
    Step1 = Table.SelectRows(Movements, each [StorageNumber] = StorageNo and [ProdNo] = ProdNo and [TransYearPeriod] < TrYrPr),
    Step2 = Table.Group(Step1, {"ProdNo", "StorageNumber"}, {{"Balance", each List.Sum([Movement]), type number}})
in
    Step2

Эти изменения не повлияли заметно на производительность.

Вывод правильный и эффективный, но не очень эффективный.

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

Эта функция запускается 1 раз для каждой строки в ссылке на мою исходную таблицу Movements, а там 380 строк.

Есть ли способ ускориться? Можно ли вместо этого использовать DAX?

Изменить: Вызов функции:

    #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "SelectBalanceFromMovements", each SelectBalanceFromMovements([TransYearPeriod], [ProdNo], [StorageNumber])),
    #"Expanded SelectBalanceFromMovements" = Table.ExpandTableColumn(#"Invoked Custom Function", "SelectBalanceFromMovements", {"Balance"}, {"SelectBalanceFromMovements.Balance"}),

powerbi powerquery powerbi-desktop m
person HIN    schedule 18.09.2019    source источник
comment
Не могли бы вы дать больше контекста о том, чего вы пытаетесь достичь здесь, и, возможно, некоторые образцы данных из вашего запроса Movements (а также статистику таблицы высокого уровня - количество строк, количество столбцов, мощность этих столбцов, на которые ссылаются). Непосредственной возможностью для оптимизации является объединение всей вашей логики фильтрации в один Table.SelectRows вместо трех последовательных. Вы можете выполнить логическое И с and на одном шаге фильтрации, относящемся ко всем 3 столбцам. Вы также можете объединить свои Step3 и Step4, определив второй столбец в вашем Table.Group, логика которого равна {"TransYearPeriod", each TrYrPr}.   -  person greggyb    schedule 18.09.2019
comment
Цель: рассчитать (инвентаризацию) остатки продуктов на складах (из списка ежемесячных транзакций), где период задается полем TransYearPeriod, продукт - ProdNo, а местоположение - StorageNumber, а ежемесячное изменение запасов - из поля Movement.   -  person HIN    schedule 19.09.2019
comment
Вы пробовали однажды создать агрегированную таблицу и объединить ее с исходной?   -  person greggyb    schedule 19.09.2019
comment
Мне приходит в голову, что вы, кажется, преследуете совокупную сумму Движений по TransYearPeriods, по ProdNo и Storage. В этом случае вы, вероятно, сможете найти специальные функции, которые сделают это за вас без отдельной таблицы и многократно повторяют ее в вызовах функций.   -  person Ryan B.    schedule 19.09.2019
comment
Попробуйте обернуть свои таблицы в Table.Buffer, а списки - в List.Buffer. Buffer помещает ваши данные в память. Также отключите фоновое обновление, если оно в данный момент включено. Это хорошие посты с множеством способов улучшить производительность. thebiccountant.com/speedperformance-aspects adatis.co.uk/buffer-m-function-in-query-editor-power-bi   -  person Jenn    schedule 27.09.2019
comment
Это должно быть возможно сделать в DAX, и это будет намного эффективнее. Можете ли вы опубликовать образец своей таблицы данных и то, как выглядит ваш желаемый результат?   -  person Jenn    schedule 27.09.2019

Ответы (1)


arrow_upward
0
arrow_downward

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

Если я прав и это поможет вам, вы можете попробовать изменить свой запрос на

let
    BufferedSource = Table.Buffer(Movements),
    // Use BufferedSource as a private, pre-computed variable within the function we're about to return
    ReturnedFunction = (TrYrPr as number, ProdNo as number, StorageNo as number) =>
        let
            Step1 = Table.SelectRows(
                BufferedSource,
                each [StorageNumber] = StorageNo and
                     [ProdNo] = ProdNo and
                     [TransYearPeriod] < TrYrPr
            ),
            Step2 = Table.Group(
                Step1,
                {"ProdNo", "StorageNumber"},
                {{"Balance", each List.Sum([Movement]), type number}}
            )
    in
        Step2
in
    ReturnedFunction

Попробуйте это (с использованием Table.Buffer и без него), а также попробуйте отключить «Быструю загрузку данных» для всех ваших запросов, потому что, по моему опыту, это заставляет вещи казаться медленнее при работе с большим количеством запросы с большим количеством данных.

person JSmart523    schedule 13.05.2020