Please fix this defect in List.Average

[doug_morris]

List.Average is not computed correctly in this code.

[
    Source = #table({"Value"}, {{null}, {1}}) & #table({"Value"}, {{2}}),
    Avg = List.Average(Source[Value])
]

It computes to 1 whereas it should be 1.5. Such incorrect List.Average calculation seems to occur anytime you are working with a non-buffered table built by combining tables. If you do an average on a column from a table like the code above or in a Table.Group, the average calculation erroneously includes nulls in the value count for the denominator.  The error does not occur with List.NonNullCount, List.Median, or List.StandardDeviation. Also, if you buffer the table or buffer the list before averaging, the error does not occur.

[Anonymous]

Hi @doug_morris 

I tried to restore the scenario you mentioned but failed, can you provide your pbix file (remove sensitive info) for reference ?

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Community Support Team _ Ailsa Tao

[doug_morris]

The steps to reproduce are simply to start with a blank query and paste in the example code shown above in the Advanced Editor. I see the error with this demonstration code and in another full-blown query where I first saw the issue. I've tested it on the latest versions of Excel and Power BI. The erroneous behavior is the same. I also see this issue acknowledged on the Power Apps platform forum.

List.Average not computed correctly on the list of... - Power Platform Community (microsoft.com)

[davidebacci]

I can reproduce. Agree, it needs fixing.