List.Average ignores nulls, but now when doing a group by

[Anonymous]

Hello

When I add a column using the below code the resulting column value ignores nulls and I get the right value.

 =Table.AddColumn(#"Changed Type", "RowAverage", each List.Average({[Step_3.1],[Step_3.2],[Step_3.3]}))

When I use the group by function and choose the Average option, it doesn't appear to ignore nulls

=Table.Group(#"Filtered Rows", {"jira_issue_key_cd"}, {{"Step_3.1s", each List.Sum([Step_3.1]), type nullable number}, {"Step_3.2s", each List.Sum([Step_3.2]), type nullable number}, {"Step_3.3s", each List.Sum([Step_3.3]), type nullable number}, {"Step_3.2a", each List.Average([Step_3.2]), type nullable number}, {"Step_3.3a", each List.Average([Step_3.3]), type nullable number},  {"Count", each Table.RowCount(_), Int64.Type}})

The key column is text, Step_3.1, 3.2, 3.3 are all whole number types.

Key	Step_3.1	Step_3.2	Step_3.3
XXX--575	2	1	1
XXX--575	2	1	1
XXX--575	1	null	null
XXX--575	1	null	null
XXX--575	1	null	null
XXX--575	2	2	2
XXX--575	2	null	null
XXX--575	1	null	null
XXX--575	2	1	1
XXX--575	2	2	2
XXX--575	1	null	null

Key	Step_3.1s	Step_3.2s	Step_3.3s	Step_3.2a	Step_3.3a	Count
XXX--575	17	7	7	0.636364	0.636364	11

Thoughts?

Thanks

[v-eqin-msft]

Hi @Anonymous ,

If you still cannot solve the issue,sorry for that, we do not found an effective solution.
As you considered, you could go to https://powerbi.microsoft.com/en-us/support/ ,scroll down and click "CREATE SUPPORT TICKET" for further help if you are a pro user.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

If so, could you please share me with your pbix file after removing sensitive data?

Best Regards,
Eyelyn Qin

[Anonymous]

I don't know of a way to do that as the data all pulls from multiple SQL Sources and when you go into PBIX it would try to refresh against those sources. If there is an article that explains how to take a snapshot of a PBIX with the current dataset (disconnected and non-refreshing) I can easily do it.

Since the problem doesn't repeat if I copy and paste the data (as both you and Daniel did), I am wondering if I need to raise a ticket with MSFT and have a screen share.

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

What's your previous step here:

On my side:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

The filtered rows step brings it down to just the single ticket that is present in the example.

The problem I have is that your example (as well as Daniel's) works fine. If I copy the pre-average result into the clipboard, click "Enter Data" and paste it, I get the correct values. Using the 'source' data, I get the incorrect answer.

[wdx223_Daniel]

i got the correct result using the same code

[Anonymous]

I see that. I just replied to Eyelyn's response as well. It's fine if I copy and paste the data to a new query and do the average, just not when I am doing it against the source information.