Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

List.Average and Select rows

Hello, could someone help me with this problem? I am trying to add this formula =List.Average(Table.SelectRows(#"added custom6", each [roles] = _[roles])[wage])   I have this table ROLES WAG...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Yes I tried Group By too, but I need to make report that will be automatic in the future (just replacing source files). I wanted to create just a reference of the query, but then I can't use merge queries, if I will use duplicate query, then it wont be automatic.??

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous 

     

    If I understand correctly, you copied the previous query and merged it with the current query.

     

    In the following test, I used the current query merged with itself, hoping to meet your needs:

    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}}),
    #"Nested Join" = Table.NestedJoin(#"Grouped Rows", {"ROLES"}, #"Changed Type", {"ROLES"}, "Table", JoinKind.FullOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Nested Join", "Table", {"WAGE"}, {"Table.WAGE"})
in
    #"Expanded Table"

     

    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.