Forum Discussion
Power Query: grouping, summing, and nulls
Let's say I have the following data in a child table: Org Score ABC 32 ABC 29 ABC 33 ABC 30 DEF 38 DEF null DEF null DEF 36 GHI null GHI null GHI null...
Hi Anonymous,
Firstly, add a custom column using the following code in your table.
= if [Score] is null then "error" else [Score]
Secondly, use “Group by” feature to calculate the scores.
Thanks,
Lydia Zhang
Hi Anonymous
I'm not sure, if I fully understood your question....
Null is treated as zero in your example.
As your data has just 3 entries in org, it sums up the score.
If you change for example one "DEF" null entry to "ttttt" null, it will return this table in the Query Editor aka Power Query:
I only want a summed up score if ALL of the scores for an org are not null; otherwise, I want it to return null.
I'm thinking of replacing all nulls with a non-numeric value to force an error, though that seems kinda kludgey to me.