Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summing Columns in a Table

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 ...
Need Help
  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    4 years ago

    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's avatar
    Anonymous
    4 years ago

    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's avatar
KT_Bsmart2gethe
Impactful Individual
4 years ago

Hi Anonymous ,

 

Please see code below:

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

 

Regard

KT

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    KT_Bsmart2gethe
    Impactful Individual
    4 years ago

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