List.Average and Select rows

Hi Anonymous 

Thanks for the reply from amitchandak and danextian .

At present, the formula you use has to query the entire table for each row, that is, the entire table has to be queried as many times as there are rows. This may be the reason for the poor performance. I used Group By to test it for your reference. Using Group By can reduce the number of rows queried each time for calculation and improve performance, but the number of result rows obtained will definitely be less than the original table (each role will only get one row of results)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKsrPSTVU0lEyNFCK1UHiGsK5RqiyIK4pnGeMwbNQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ROLES = _t, WAGE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ROLES", type text}, {"WAGE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ROLES"}, {{"Average", each List.Average([WAGE]), type nullable number}})
in
    #"Grouped Rows"

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.