You’re offline. This is a read only version of the page.
11
I see an issue with how averages are being computed in a certain scenario and have written a simple code example to illustrate the problem. Avg1 computes as 1 and Avg2 computes as 1.5. (Avg 2 is correct.)
If Source were defined as a single table (not combining two tables), the issue does not occur. Notice that buffering fixes the issue. Also, it only seems to happen with List.Average. (I tested List.Median, List.NonNullCount, List.Sum, and List.StandardDeviation. They give correct results with or without buffering.)
I've seen the same error in another place and believe it is a systematic flaw any time you are computing an average on a column in a non-buffered table which was formed by combining tables. It happens also if you do a List.Average in a Table.Group function call.
[
Source = #table({"Value"}, {{null}, {1}}) & #table({"Value"}, {{2}}),
Avg1 = List.Average(Source[Value]),
Avg2 = List.Average(List.Buffer(Source[Value]))
]
I've submitted this defect to the Microsoft Power BI Community forum. We'll see what happens there.
Thank you, Pete. Has this issue been raised as a serious product defect that needs attention? It's a really bad bug.
I would recommend another workaround as an alternative to removing nulls. A developer may choose to buffer the underlying table. This prevents the defect from occuring. It also has the advantage of allowing subsequent Table.Group steps to be edited through the settings UI, which would not be the case if List.RemoveNulls were used in a Table.Group aggregation.
It's interesting to note that this defect only occurs with a list generated by drilling down on a column in a table built by combining tables. For example, List.Average(List.Combine({{null, 1}, {2}})) does not have the problem. This is another reason I like buffering the combined table. You can document why you are buffering the table and be sure any subsequent edits to the query will not need to worry about the averaging defect.
Table buffering might not always be a good option, but it seems to sometimes improve performance in this very scenario of queries using combined tables. Unless this defect is fixed, I will make it a practice of buffering all combined tables unless there are negative performance implications. I feel like I almsost have to do this because such unexplainable behavior makes me wonder whether there are other defects lurking out there with combined tables.
#1
mmbr1606
9
Super User 2025 Season 1
#2
stampcoin
7
#3
SD-13050734-0
6
Share
Report
All responses (
Answers (
