topic Re: Summing Columns Based on Column Header String in Power Query

Summing Columns Based on Column Header String

jaiken — Tue, 15 Mar 2022 20:39:29 GMT

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

Re: Summing Columns Based on Column Header String

artemus — Tue, 15 Mar 2022 23:36:02 GMT

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.

Re: Summing Columns Based on Column Header String

Vijay_A_Verma — Wed, 16 Mar 2022 05:27:22 GMT

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"

Re: Summing Columns Based on Column Header String

jaiken — Wed, 16 Mar 2022 14:22:12 GMT

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.

Re: Summing Columns Based on Column Header String

CoffeeAddictMk2 — Wed, 06 Mar 2024 07:53:37 GMT

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