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":
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)  .... })
Group By Error.jpg