

September 29, 2021

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.


Trusted Members
Moderators
Power BI

January 31, 2022

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.....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.

Answers Post


Trusted Members
Moderators

November 1, 2018

1 Guest(s)
