PowerBI / PowerQuery : Speed up slow Invokable Custom Function (for calculating balances)

Question

The function makes three sort operation and sums the result for each of the rows in the source table (Movements).

Even though the rows are grouped and aggregated in the movements table, this results in many filtering steps and is expensive.

Is there a better way?

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

Edits:

Changed by suggestion from greggyb of combining the filter steps, and I did not need the period column:

(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

These changes did not noticeably change performance.

The output is correct and effective, however not very efficient.

I believe the slow processing comes not from the aggregation, but rather creating multiple copies of the data in memory during the filter.

This function runs 1 time per row in a reference of my source table Movements, and there are 380 rows.

Is there a way to speed up? Is it possible to use DAX instead?

Edit: The calling of the function:

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

Can you give more context on what you're trying to achieve here, and perhaps some sample data from your Movements query (as well as high level table stats - row count, column count, cardinality of these columns being referenced). An immediate opportunity to streamline is to put all your filtering logic into a single Table.SelectRows instead of 3 successive ones. You can logically AND with and in a single filtering step referring to all 3columns. You could also merge your Step3 and Step4 by defining a second column in your Table.Group whose logic is {"TransYearPeriod", each TrYrPr}. — greggyb
Goal: Calculate (Inventory) Balances of products at storage locations (from a list of monthly transactions) where period is given by the field TransYearPeriod, product by ProdNo and location by StorageNumber and the monthly change in stock from the field Movement. — HIN
Have you tried making your aggregated table once and merging that with the original? — greggyb
It occurs to me that you seem to be after a cumulative sum of Movements by TransYearPeriods, over ProdNo and Storage. If that's the case, you can probably find specific functions to do that for you without a separate table and repeatedly iterating over it in function calls. — Ryan B.
Try wrapping your tables in Table.Buffer and your lists in List.Buffer. Buffer puts your data into memory. Also turn off background refreshes, if it is currently enabled. These are good posts with a lot of ways to improve performance. thebiccountant.com/speedperformance-aspects adatis.co.uk/buffer-m-function-in-query-editor-power-bi — Jenn

JSmart523 JSmart523 · Accepted Answer · 2020-05-13T19:20:30

Is it possible it's re-evaluating Movements each time the function is called? I've had luck speeding up my queries by making sure all variables wwithin functions are as pre-computed as possible.

If I'm right and this would help you, you might want to try changing your query to

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

Try that (with and without using Table.Buffer), and also try turn off "Fast Data Load" for all of your queries because in my experience it's made things seem slower when dealing with a large number of queries with lots of data.

PowerBI / PowerQuery : Speed up slow Invokable Custom Function (for calculating balances)

1 Answers