Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Syndicate_Admin
Administrator
Administrator

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

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

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

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

View solution in original post

5 REPLIES 5
Syndicate_Admin
Administrator
Administrator

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
Administrator
Administrator

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.

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Kudoed Authors