Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
GHI | 34 |
Nulls mean the score hasn't been collected yet.
What I need is to sum up the scores by org, but those orgs that have null scores to return a null, thus indicating that they're not 'finished' yet. When I group and sum in Power Query, it's ignoring the nulls. I can't just filter out the nulls, because then it would look as if all of the orgs are 'finished'.
Is there some way in Power Query to force Sum to return nulls on a grouping? I'm wanting to do this in Power Query to help limit the amount of data sent to the report from the source. The line I have is '= Table.Group(#"Promoted Headers", {"Table1"}, {{"Org", each List.Sum([Score]), type number}})'
I hope this makes sense. TIA
Solved! Go to Solution.
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,
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
Something that might help get you there, try sorting your Score column and then using a GroupKind.Local in your Table.Group function. This will return local groups and you will get nulls back from your Table.Group statement. You'll have to do some cleanup but this might get you close enough to what you want to get to a solution.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |