Forum Discussion
Reference Column Names for Single Month Deviation Calculation
Dear PowerQuery enthusiasts,
some weeks ago I discovered the Record.SelectFields function to build moving Year To Date summation based on a list of Fields to summarize.
Building on this I wanted to calculate a single month Act vs. Plan deviation. The two fields that must be used for that I have readily listed in ev_Mon. If I address in the query 'td' the name of the actual column, which is list entry zero
Table.AddColumn(Quelle, "Act03_Test", each List.Sum(Record.ToList(Record.SelectFields(_, ev_Mon{0}))))
I get a result.
If now I want to calculate the deviation by additionally using the Field Name for 'Plan' which is entry {1} then it fails
Table.AddColumn(Add_Act03, "SingleMonthDeviation", each List.Sum(Record.ToList(Record.SelectFields(_, ev_Mon{0}))) - List.Sum(Record.ToList(Record.SelectFields(_, ev_Mon{1}))))
Is there a simple way I can reference column names from the list [ev_Mon] to use them in a simple formula 'Act vs. Plan = 2023 03 Act – 2023 03 Plan'. The full data can be found here https://c.gmx.net/@324888734501700174/3tYxwA5HTE2JiDt9phswfg
The referencing has to be from the list, because next month this list will switch to 04/ April.
Thank you for your direction and help!
Best regards, Andreas
Hi Goodkat
Before we start it maybe good to know that aggregating values that include a null, will return null
Furthermore it appears you are using this to returns a single field value: List.Sum(Record.ToList(Record.SelectFields(_, ev_Mon{0})))
That means it can be replaced with: Record.Field(_, ev_Mon{0})
To perform an aggregation over fields while dealing with nulls you can apply coalesce to return a 0 (read zero) instead, like so: Record.Field(_, ev_Mon{0}) ?? 0 - Record.Field(_, ev_Mon{1})
I hope this is helpful
2 Replies
Hi Goodkat
Before we start it maybe good to know that aggregating values that include a null, will return null
Furthermore it appears you are using this to returns a single field value: List.Sum(Record.ToList(Record.SelectFields(_, ev_Mon{0})))
That means it can be replaced with: Record.Field(_, ev_Mon{0})
To perform an aggregation over fields while dealing with nulls you can apply coalesce to return a 0 (read zero) instead, like so: Record.Field(_, ev_Mon{0}) ?? 0 - Record.Field(_, ev_Mon{1})
I hope this is helpful
Dear dekorte,
Please accept my apologies for replying late. I was not every day in front of my notebook. Today I could test it for the first time in my productive dataset.
Thank you for taking the time and directing me to the right direction! It works! It is so cool! 'Record.Field' I did not know about, but it solves exactly my problem of referencing the columns.
The remark about coalesce with '?? 0' is also an essential new information that I implemented today and it pefectly solves the problem of working with null.
Best regards, Andreas
