Summing Columns in a Table

[Anonymous]

I have a Table with a Number of Columns  Col1, Col2, Col 3 and I want to add a Column that is the Sum so I am Using List.Sum({[Col1],[Col2],[Col3])

However I want to make this Generic, so that If a Col4 is added it automatically Includes it in the sum Without having to ManualFix the Query

I have a Iist of the cols I want to add {"Col1", "Col2", "Col3"} but I cannot get this to work without Resorting to Unpvoting and Grouping which is a HUGE perfromance Hit

Any Suggestion as to How I Could achive this

[KT_Bsmart2gethe · Accepted solution]

Hi @Anonymous ,

Scenario:

If you want to keep any columns other than "EBIT",

1. Reorder Column to move all "EBIT" column to the end:

Code: Table.ReorderColumns(#"Changed Type",List.Combine({List.Select(Table.ColumnNames(PreviousStep), each not Text.Contains(_,"EBIT",Comparer.OrdinalIgnoreCase)), List.Select(Table.ColumnNames(PreviousStep), each Text.Contains(_,"EBIT",Comparer.OrdinalIgnoreCase))}))

then,

2. Add a custom column and add sum lastN columns:

Code: List.Sum(List.LastN(Record.ToList(_),List.Count(List.Select(Table.ColumnNames(PreviousStep), each Text.Contains(_,"EBIT",Comparer.OrdinalIgnoreCase)))))

[Anonymous · Accepted solution]

WOW that is Awesome. Not Intuative - I would never have figured this out

Two Fantastic Snippits of Code  that I am Going to Use Over and Over Again

[KT_Bsmart2gethe]

Hi @Anonymous ,

Please see code below:

Replace {"Col1", "Col2" .....} with List.Select(Table.ColumnNames(PreviousStep), each Text.Contains(_, "Keyword"))

Regard

KT

[Anonymous]

Thanks for the Reply - You Code Does Select the Column names (actually Betterr than My Methd) but I does Rurtn the Vlaues that I Need to Pass to List.SUM - Which is what I an Strugling With

i.e the tableis 

List is {"EBIT1","EBIT2,"EBIT3")

I need it to be {2,3,0} tso I can Pass it to List.Sum()

[KT_Bsmart2gethe · Accepted solution]

Hi @Anonymous ,

Scenario:

If you want to keep any columns other than "EBIT",

1. Reorder Column to move all "EBIT" column to the end:

Code: Table.ReorderColumns(#"Changed Type",List.Combine({List.Select(Table.ColumnNames(PreviousStep), each not Text.Contains(_,"EBIT",Comparer.OrdinalIgnoreCase)), List.Select(Table.ColumnNames(PreviousStep), each Text.Contains(_,"EBIT",Comparer.OrdinalIgnoreCase))}))

then,

2. Add a custom column and add sum lastN columns:

Code: List.Sum(List.LastN(Record.ToList(_),List.Count(List.Select(Table.ColumnNames(PreviousStep), each Text.Contains(_,"EBIT",Comparer.OrdinalIgnoreCase)))))