Creating a Calculated Column to Sum a selection of other columns

[Syndicate_Admin]

The title I've given this topic is probably not as good as it might be...hopefully I can explain better here.

I have a table, Contingency, in Power Query. It's pretty straightforward with Project Number, Project Name and then multiple columns of Forecast figures over a number of years...with each year split into 13 periods. So 13 columns per year and lets say 10 years worth of data = 130 columns. I need to sum those figures for each particular year...so if the Column Header starts with 2024 then sum those columns. Sounds simple, thought I knew what I was doing...but I can't make it work for the life of me...even the 'all knowing' CHAT GPT comes up with code which errors out.

An important consideration is that the source of the table does not output a column if that column is empty. So although you would expect Period 1 to Period 13 to be created if Period 2 and 3 are empty then they columns will not be created so you have 11 rather than 13 for that year.

Here is one of many attempts I've made, one of the more simpler ones at least:
List.Sum(Table.SelectColumns(Contingency, {"2023-2024 P01", "2023-2024 P02", "2023-2024 P03", "2023-2024 P04", "2023-2024 P05", "2023-2024 P06", "2023-2024 P07", "2023-2024 P08", "2023-2024 P09", "2023-2024 P10", "2023-2024 P11", "2023-2024 P12", "2023-2024 P13"}, MissingField.Ignore))

Any ideas?

Thanks in advance.

[AlienSx]

    unpivot = Table.UnpivotOtherColumns(Contigency, {"Project Number", "Project Name"}, "Year", "Value"),
    get_year = Table.TransformColumns(unpivot, {"Year", (x) => Splitter.SplitTextByDelimiter("-")(x){0}}),
    group = Table.Group(get_year, {"Project Number", "Project Name", "Year"}, {{"Total Year", each List.Sum([Value]), type number}}),
    pivot = Table.Pivot(group, List.Distinct(group[Year]), "Year", "Total Year")

[Syndicate_Admin]

Thanks for that Sebastian....I'll give it a try but I can't help but thinking what I'm asking for and what my original code is attempting to do is not totally mad.

Thanks again...I'll let you know how I get on.

[Syndicate_Admin]

Hi psadd,

i hope i understood your questions correct, you have a lot of values in columns with alternating names and need to get a sum of those?

To be honest, it sounds like a data structure problem to me.

Maybe you can achieve your goal by using "unpivot" and "groupBy" ?

Here is a quick table with values in multiple columns:

This is how it looks after using "unpivot"

And that after group by:

Even if the number of columns is changing, you would always get the sum of all columns (P01 - Pxx) for all "cities".