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

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

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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)))))