Creating a Calculated Column to Sum a selection of other columns

(0) Share

Report

Posted on by psadd2

133

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.

Categories:

Power Query

I have the same question (0)

All responses (3)

Answers (0)

Sebastian030 86 on at

Like (0)

Report

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".

Was this reply helpful? Yes No
psadd2 133 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
psadd2 133 on at

Like (0)

Report

Thanks...that's an interesting one...I need to get my head around what that's doing.

I managed to get a quick fix by another process though. The key problem was that we had no idea what columns would be present in the source or not, hence the need to 'ignore' missing columns. However I created an empty table that contained every column that there should be...in other words a complete 'template'. I then Appended my existing table to that and the Append added data when and where the relevant column was....so now I have a 'full' table with some empty columns. Then it was easy enough to do a [colA]+[colB]+[colC] etc.
I will try the method you propose though as it looks slightly more elegant than my 'manual' method.

Thanks

Was this reply helpful? Yes No