Force "List.Average" to ignore null cells

[Anonymous]

Hello,

Is there a way in Power Query to force the "List.Average" function to ignore "null" cells?

I know it should be doing this by default but I've been having trouble just today where getting a daily average of hourly values using "Group By" in Power Query, it's been including null, bringing down the average. This only happens if I'm initially appending two queries (one static excel from a LAN + Data Lake via Azure / Kusto). Strangely enough, I can "Group By" in both tables individually, but it's only if I merge the queries first and then try to "Group By" on the single table.

An excerpt from the advanced editor, showing what is written when I use "Group By":

let
Source = Table.Combine({#"PI 2019 Excel", #"Data Lake Hourly (2)"}),
#"Grouped Rows" = Table.Group(Source, {"Start of Day"}, {{"K1_WY-1120-1 Cap Util", each List.Average([#"K1_WY-1120-1 Cap Util"]), type number}, ...(and so on, there are a lot of columns)  .... })

Thanks!

[camargos88]

Hi @Anonymous ,

I believe this functions handles the null values.

Can you share this pbix, it can help to understand what is happening.

[AlB · Accepted solution]

Hi @Anonymous 

as @camargos88 said, List.Average( ) should ignore the nulls already, so probably something else is going on here. However, perhaps, you can try filtering out those nulls before applying the Average

List.Average( List.Select( [#"K1_WY-1120-1 Cap Util"] , each _<> null ) )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[Anonymous]

Thank you, putting that filter into the List.Average is what I was looking for. I also included _ <> "" and that seemed to work well!

List.Average(List.Select([#"K1_WY-1120-1 Cap Util"], each _<> "" and _ <> null))