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

[Syndicate_Admin]

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

[Syndicate_Admin · Accepted solution]

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/335824...

[Syndicate_Admin]

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({{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.

[BA_Pete]

I don't know if this has been reported or not as it's never been an issue for me.

I don't often use Power Query/M for calculations as DAX is far more efficient and it keeps such operations more broadly within the use-case and design of each language.

You're right to identify that buffering isn't always a good option. It could cause significant performance issues during query refresh, especially when used 'frivolously' in this way. That being said, I think your suggestion to document/comment code when this technique must be used is very prudent and would certainly also be my recommendation to ensure clarity for future users of the code.

Pete

[BA_Pete]

I don't know if this has been reported or not as it's never been an issue for me.

I don't often use Power Query/M for calculations as DAX is far more efficient and it keeps such operations more broadly within the use-case and design of each language.

You're right to identify that buffering isn't always a good option. It could cause significant performance issues during query refresh, especially when used 'frivolously' in this way. That being said, I think your suggestion to document/comment code when this technique must be used is very prudent and would certainly also be my recommendation to ensure clarity for future users of the code.

Pete

[BA_Pete]

Hi @Syndicate_Admin ,

This is a known issue. See this thread where the solution is to filter the list passed to List.Average by using List.Select:

https://community.fabric.microsoft.com/t5/Power-Query/Force-quot-List-Average-quot-to-ignore-null-ce... 

You could also use List.RemoveNulls which would be slightly tidier and wouldn't require buffering to memory, something like this:

[
    Source = #table({"Value"}, {{null}, {1}}) & #table({"Value"}, {{2}}),
    Avg1 = List.Average(Source[Value]),
    Avg2 = List.Average(List.Buffer(Source[Value])),
    newAvg = List.Average(List.RemoveNulls(Source[Value]))
]

Pete