Query SQL table with another Query

[Anonymous]

Hey guys,

Not sure if this is possible but just looking for some help.

Let's say i have two tables in Power query -  one is data loaded from an Excel file and the other is loaded from an SQL Database.

Now let's say the SQL table is indexed on OrderRef and I have an Excel file that contains an ever changing list of OrderRef, how would I use the OrderRef collumn in my loaded Excel sheet to query the OrderRef collumn in the SQL Table? 

I've looked into using the List.Contains function and setting it as a filter but not sure if I'm on the right track.

Thanks in advance!

S

[edhans]

List.Contains would work, but you might just want to use a MERGE and use an Inner Join between the Excel and SQL table

[Anonymous]

Hey @edhans  Thanks for the suggestion!

I've tried using the merge option but the query tries to load everything from the database even though I'm selecting the same two collums and my look up table only has 5000 references. The database gets to around 500k and ends the query

Is there a way to prevent this?

Thanks

S

[edhans]

You can try List.Contains using the method described here.

THat will fold, but it uses an IN statement in SQL, which may not be any faster.

Something somewhere has to load the entire database to see what should be part of the data returned. It has to evaluate EVERY record, so if you can get a merge to fold (which you cannot with Excel data) then the server would do the merge and return only what you need. If you cannot get it to fold, then you have to let Power Query load the whole thing.

[Anonymous]

Great, thank you. I'll try this method today and update if it's successful. As suggested on the site I will download the test server and data and try it on there first!