Slow Running Total and Total from a Table Column

[Hashiru]

Hi all below is are code snippet of two queries

FnGroupedRunningTotal - Function

(MyTable)=>
let

    #"Added Index1" = Table.AddIndexColumn(MyTable, "Index.1", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index1", "FedRunningBal", each List.Sum(List.Range(#"Added Index1"[#"Federal Reimb."],0,[Index.1]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "FedBalance", each List.Sum(#"Added Custom"[#"Federal Reimb."])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"FedRunningBal", type number}})
    //#"Added Custom1" = Table.AddColumn(#"Changed Type", "FedBalance", each List.Sum(#"Added Custom"[#"FedRunningBal"]))
in
    #"Added Custom1"

Main Query from which function is invoked

...


#"Grouped Rows1" = Table.Group(#"Expanded BillingRules", {"ProjectActivityFundALI"}, {{"RunningT", each _, type table}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows1", "Totals", each FnGroupedRunningTotal([RunningT])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"RunningT"}),

...

Introducting this function to calculate the total and running totals for each group incredibly slow down the running time of the query. I have to invoke this function again to calculate the running total and totals for another set of data to be mashed up with the main data.

How do i optimize my solutions?

[dax]

Hi @Hashiru , 

You could try to use Table.buffer which might will optimize the performance to see whether it work or not. You could refer to Running Total in Power Query  and Memory efficient clustered running total in Power BI  for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hashiru]

Thanks to the dax community team, I tried the Mike Gavin solution and plug off the function I used before and change the invocation as follows:

#"Added Index" = Table.AddIndexColumn(LaborCostsBillingRulesKey, "Index", 1, 1),
BufferedTable = Table.Buffer(#"Added Index"),
#"Added Custom" = Table.AddColumn(BufferedTable, "RT", (OT) => List.Sum(Table.SelectRows(BufferedTable,(IT) => IT[FundScopeALIProjActivity]=OT[FundScopeALIProjActivity])[#"Federal Reimb."]))

The queries run in about 20 minutes but the load to either the worksheet or the data model is still a problem. After all the manipulation I need to have the output in csv file. This mean that I need to load the data into excel eventually.

How do I improve on the load time as It takes 12 hours plus to do the load of 120,000 rows?