CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktopm
HIN
HIN

Reputation: 21

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

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"}),

Upvotes: 1

Views: 1283

Answers (1)

JSmart523
JSmart523

Reputation: 2497

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.

Upvotes: 1

Related Questions