Forum Discussion

Hashiru's avatar
Hashiru
Helper I
6 years ago

Slow Running Total and Total from a Table Column

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?

Tips and Tricks

2 Replies

    • Hashiru's avatar
      Hashiru
      Helper I
      6 years ago

      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?