Dynamic Sum of Columns is slow

[Goodkat]

Dear Power Query Enthusiast,

last week I had an idea of how to further automate my datamodel. I have some aggregations over columns that I need to maintain manually every month that the year progresses. I want to change that to a List.Sum command that uses another table to reference the fields to aggregate.

The manual formula as is: Add_Sum = Table.AddColumn(Convert, "Sum Manual", each List.Sum({[#"2023 01 Act"], [#"2023 02 FC1"], [#"2023 03 FC1"], [#"2023 04 FC1"]}), type number)

New automated formula: Add_Sum = Table.AddColumn(Quelle, "Sum Auto", each List.Sum(Record.FieldValues(Record.SelectFields(_, ColumnsList))), type number)

And now the problem: The List.Sum / Record.FieldValues / Record.SelectFields combination is way slower than the simple List.Sum formula. If you compare both the manual one runs for 25.630 records some 2 seconds; the new formula about 10 seconds (slower than an Excel formula). As the datamodel the new formula shall go into is bigger, the slow down is a deal breaker.

And important: Back in April when I first tested the new formula it ran nearly as fast as the manual one. I tested only briefly in a simpler file. Somewhen around last week my windows 10 / excel 365 notebook received an update I could not prevent. Now in Excel and Word I have accessibility checks and an altered appearance of spell check. Could it be that for some reason the update is slowing down certain Power Query functionality?

Sample data: https://c.gmx.net/@324888734501700174/vpfqx2nFSpeZnKPTw8Kj6w

Could someone run both command lines in comparison and tell, if the slow pattern auf the new formula vs. the simple List.Sum Formula also appears on your machine?

Versions used:

Power Query 2.108.986.0 (32 Bit)

Excel 365 Version 2208 Build 15601.20626

Thank you so much in advance!

Best regards, Andreas

[Goodkat]

Dear lbendlin,

Wow! I just checked it briefly in my test data kit and the result is thrilling! At a first sneak view it looks correkt and was rapidly fast. So fast I initially thought it did not work. I will check on Monday in full depth (today we have a family visit and my wife will freak out when I spend half the day in front of my notebook 😉 ) and also read further posts on List.Buffer. But solely from the name I would say that is the trick I did not know.

Thank you so much for sharing your knowledge!

Have a great, great weekend whereever you are! Best regards, Andreas

PS: Once I have confirmation it works, I will 'accept as solution'

[lbendlin · Accepted solution]

Does Excel Power Query support List.Buffer?

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Power Query Sum Test.xlsx"), null, true),
    data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"2023 01 Act", type number}, {"2023 02 FC1", type number}, {"2023 03 FC1", type number}, {"2023 04 FC1", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sum Manual", each List.Sum({[#"2023 01 Act"], [#"2023 02 FC1"], [#"2023 03 FC1"], [#"2023 04 FC1"]})),
    ColumnsList = List.Buffer(Columns[Spalte1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sum Auto", each List.Sum(Record.FieldValues(Record.SelectFields(_, ColumnsList))))
in
    #"Added Custom1"