Answered

List.Average not computed correctly on the list of values in a column from a non-buffered table created by combining tables

(0) Share

Report

Posted on by doug_morris

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]))
]

Categories:

Power Query

I have the same question (0)

All responses (2)

Answers (1)

Sort by

doug_morris 11 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

doug_morris 11 on at

Like
a
(0)

Report
Copy link

Link copied!

I've submitted this defect to the Microsoft Power BI Community forum. We'll see what happens there.

https://community.fabric.microsoft.com/t5/Issues/Please-fix-this-defect-in-List-Average/idc-p/3358240

Was this reply helpful? Yes No