Summing Columns Based on Column Header String

[jaiken]

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 columns and do a simple sum, but as more columns come as 2022 continues, I need Power Query to automatically add them. So right now, it's simply 202201, 202202, and 202203 that need to be added together to create a "Total 2022" column. Once April starts and I load a 202204 column, it will need to automatically add the column 202204 to the "Total 2022" column. 

Item	202110	202111	202112	202201	202202	202203
A	1	1	1	2	2	2
B	2	2	2	4	4	4
C	3	3	3	6	6	6
D	4	4	4	8	8	8
E	5	5	5	10	10	10

[CoffeeAddictMk2]

Thanks artemus.  does the job i need too...

[Vijay_A_Verma]

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"

[artemus · Accepted solution]

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]

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.