Forum Discussion

jaiken's avatar
jaiken
Frequent Visitor
4 years ago
Solved

Summing Columns Based on Column Header String

Hello, I am trying to create a couple columns here. They specifically need to be a sum total of the columns that have the header beginning with "2021" and "2022"   I know I can just select the colu...
Tips and Tricks
  • artemus's avatar
    artemus
    4 years ago

    The new column would like like:

    List.Sum(List.Transform(List.Select(Record.FieldNames(_), each Text.StartsWith(_, "2021") or Text.StartsWith(_, "2022")), (name) => Record.Field(_, name)))

     

    Alterativly,

    1. Transpose the table

    2. select rows where the first column starts with 2021 or 2022

    3. transpose the table back.

artemus's avatar
artemus
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

The new column would like like:

List.Sum(List.Transform(List.Select(Record.FieldNames(_), each Text.StartsWith(_, "2021") or Text.StartsWith(_, "2022")), (name) => Record.Field(_, name)))

 

Alterativly,

1. Transpose the table

2. select rows where the first column starts with 2021 or 2022

3. transpose the table back.

  • jaiken's avatar
    jaiken
    Frequent Visitor
    4 years ago

    Very nice... I wrote it this way in order to get a 2022 Column and it works very nicely: 

     

    List.Sum(List.Transform(List.Select(Record.FieldNames(_), each Text.StartsWith(_, "2022")), (name) => Record.Field(_, name)))

     

    Thank you for your fast work.