Forum Discussion
Query runs very slow with Table.SelectRows
Hi & thank you for reading,
I have an issue with a Power Query. I have the following M-Code running slow. I dont think I can explain that well but hopefully the code will makes sense? Essentially I have a job number with multiple lines that I have nested in one cell JN12345 -A-B-C (JN12345 being the job number and A-B-C is code for the lines on the job number). I have VIN numbers that have many job numbers. What the below does is for each job number I have a matched of all other job numbers that have the VIN in common except if they match.
let
Source = Table.NestedJoin(#"WIP", {"VIN"}, #"ROColl", {"VIN"}, "ROColl", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let currentRO = [Job No], currentSales = [Sale Type] in
Table.SelectRows([ROColl], each [Job Number] <> currentRO & " - " & currentSales)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ROColl"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Matching Open RO's", each Text.Combine(Table.Column([Custom],"Job Number"),"#(lf)")),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Matching Open RO's", type text}})
in
#"Changed Type"
It does exactly what I require it to. However it seems ok until it gets to:
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Matching Open RO's", each Text.Combine(Table.Column([Custom],"Job Number"),"#(lf)")),
It takes over 10 minutes to refresh possibly more - I am not sure if the time is being consumed in the previous line or just this one?
I have tried buffering the table but not sure if I am going in the correct direction or just a little bit out on my syntax I was using the help of this post:
https://powerpivotpro.com/2019/02/powerquerymagic-conditional-joins-using-table-selectrows/ (Thank you Justin)
The code below I was just trying to get a result and work from there?
let
Source = #"WIP",
BufferRO = Table.Buffer(#"ROColl"),
#"Add RO's" = Table.AddColumn(Source, "RO List", each List.Distinct( Table.SelectRows(
BufferRO, (Magic) =>
Text.Contains( [Job No] , Magic[JobNumber] )
)[JobNumber2]))
in
#"Add RO's"
Any help would be magnificant.
Thank you
Hello Coxy_CMV
you are here maneuvring a lot with tables and that could it makes slow. However try this more compact code, if it has some impact on the performance and let me know. By the way... how many rows you have in #"WIP"-table?
let Source = Table.NestedJoin(#"WIP", {"VIN"}, #"ROColl", {"VIN"}, "ROColl", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(Source, "Matching Open RO's", each let currentRO = [Job No], currentSales = [Sale Type] in Text.Combine(Table.SelectRows([ROColl], each [Job Number] <> currentRO & " - " & currentSales)[Job Number],"#(lf)" )), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ROColl"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Matching Open RO's", type text}}) in #"Changed Type"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
1 Reply
Hello Coxy_CMV
you are here maneuvring a lot with tables and that could it makes slow. However try this more compact code, if it has some impact on the performance and let me know. By the way... how many rows you have in #"WIP"-table?
let Source = Table.NestedJoin(#"WIP", {"VIN"}, #"ROColl", {"VIN"}, "ROColl", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(Source, "Matching Open RO's", each let currentRO = [Job No], currentSales = [Sale Type] in Text.Combine(Table.SelectRows([ROColl], each [Job Number] <> currentRO & " - " & currentSales)[Job Number],"#(lf)" )), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ROColl"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Matching Open RO's", type text}}) in #"Changed Type"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy