Forum Discussion
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 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 |
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.
4 Replies
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.
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.
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"Thanks artemus. does the job i need too...
