Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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)))))
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
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
Hi @Anonymous ,
Please see code below:
Replace {"Col1", "Col2" .....} with List.Select(Table.ColumnNames(PreviousStep), each Text.Contains(_, "Keyword"))
Regard
KT
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()
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)))))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
16 | |
15 | |
12 |