Sum of month columns by year

[AlexJones]

Hello,

I'm new with power query and I try to do the sum of each month column by year.

For example, I have columns : jan. 2025, feb. 2025, ..., dec. 2025 and I want to sum all by row inside a 2025 column.

Same for 2026, 2027...

I tried this :

= List.Accumulate({Number.From(#"ParamYear")+1..2040}, #"Source", (state, current) => Table.AddColumn(state, Number.ToText(current), each List.Sum(List.Transform({"jan.", "feb.", "mar.", "apr.", "may", "june", "july", "aug.", "sept.", "oct.", "nov.", "dec."}, each Table.Column(state, _ & " " & Number.ToText(current))))))

I think I am close to the solution, I tried to implement this solution

Thanks you in advance,

Alex

[AlexJones]

Example of the dataset :

If possible, I want also to be able to do the sum by year if a user delete a column like "mar. 2024", the sum need to be done on other existing columns

[zenisekd]

Wouldn't it make more sense to transform the table so that you had 4 Columns? ID, Month, Year and Value. It is easy to transform it this way and you can easily do data manipulations in Dax etc....  

[AlexJones]

Data come from an export of another software, I can't change the format. And there is a lot of other columns that are processed to generate the result.

[AlienSx]

Hello, @AlexJones first unpivot all columns but ID. Then

 - to get totals by mon. year: group by that column

 - to get totals by year: split "mon. year" column by space and group by new "year" column. 

[AlexJones · Accepted solution]

I find a solution before I see your comment :

List.Accumulate({2022..2031}, #"Source", (state, current) => Table.AddColumn(state, Number.ToText(current), (record) => List.Sum(List.Transform({"jan.", "feb.", "mar.", "apr.", "may", "june", "july", "aug.", "sept.", "oct.", "nov.", "dec."}, (month) => Record.FieldOrDefault(record, month & " " & Number.ToText(current), 0)))))

It was easier by not using each _ but only lambda with specified arg name.

Thanks all !

[zenisekd]

Could you share a sample / printscreen of your dataset in PQ?