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.

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Solution file uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuSUpknlbzmi8ikqU?e=RzXqNw 

The solution is completely dynamic - Remove add new months, years or rows. 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Year Title", each Text.Start([Attribute],4)&" Total"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Item", "Year Title"}, {{"Sum", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Year Title"]), "Year Title", "Sum", List.Sum)
in
    #"Pivoted Column"