Power Query: grouping, summing, and nulls

[Anonymous]

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

[v-yuezhe-msft · Accepted 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

[Greg_Deckler]

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.

[Anonymous]

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:

[Anonymous]

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.