I need to add columns that sum based on column name. The columns present may vary but will be 4 digit codes (GL account numbers).
I have a solution that requires unpivot and merge for each combination, but I have six combinations and 60K rows so I am looking for a way to do it with lists instead in hopes of improved performance.
I need to get a list for the List.Sum function as a list rather than as text.
Example: Sum values by row for all columns that start with 47 and end in 1 (47x1 column)
Not dynamic: = Table.AddColumn(Source, "Addition", each List.Sum({[4701], [4721], [4751]}), type number)
I can get a dynamic list in a step to call, but it brings in text values "4701", "4751", "4781" rather than [4701], [4751], [4781] format for the List.Sum to accept it.
I can't find a way to convert a list from text into just a string of values.
I attached a sample data with hard coded examples. Any suggestions for how to do this with lists/efficiently, would be appreciated. Thanks.
You didn't attach a file but I believe it was clear what you needed.
Google a bit and come to this site:
https://www.bookkempt.com/2019/03/sum-all-except-certain-columns-in-power.html
Used the method described to construct the attached file and learned a great deal from it myself. Perhaps you can use it in your own.
Thank you! I thought I had attached a file! I appreciate your reply and am headed to check it out!
With a list of matching column names, you can get the sum using something like:
List.Sum(Record.ToList(Record.SelectFields(_, MatchingColumnNames)))
Thank you Velouria. I can't mark two answers 🙁
No problem - just wanted to mention it as an alternative. 🙂
