Forum Discussion
List.Average and Select rows
let
role = [ROLES]
in List.Average(Table.SelectRows(#"Changed Type",each [ROLES] = role)[WAGE])
I simply updated this part of your formula with the correct syntax:
each [roles] = _[roles]
[ROLES] is a column assigned to the role variable. This should be done whenver you're trying to access a column outside the current scope which in this case is #"Changed Type". Whatever the value of ROLES is it will be assigned to this variable and will be used to filter the previous step which result is carried over in the current step. By not doing this you are simply telling Power Query to return true for all rows.
Hey again! 🙂
So the solution is working, but after I placed that formula, the Power Bi started to be slow, it took so long to make some action. It is fast when I open previous steps. Is there any chance to fix it or am I forced to think about different formula / method.
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.??
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Anonymous,
thank you for your reply!
Unfortunately, I can not open your file.pbix, we have another version of PowerBi in company. Could you refer me what that code is doing? Too many new characters for me and I am kinda lost 🙂 Really apreciate your help.
Hi Anonymous
#"Nested Join" = Table.NestedJoin(#"Grouped Rows", {"ROLES"}, #"Changed Type", {"ROLES"}, "Table", JoinKind.FullOuter)This step is to merge "Changed Type" and "Grouped Rows", which realizes the merging of the current query itself.
What is your Desktop version?
Best Regards,
Yulia Xu