Power BI Filter/Restrict Load Based on Existing Values

[jcampbell474]

I guess this could be categorized as a cross-post to this, but I'm framing it with different context due to potential confusion with another product being mentioned (in the other thread).

Hoping someone can help.

I have a relatively small SQL dataset that contains keys.  On another server, the same key exists, but the table is huge (and the server is slow).  I need Power BI to use the key in the 1st table and only load records from the 2nd table that contain the key from the first table.

Here is an example of what we need.  It is too slow and often times out (again, the table is huge).

SELECT
  a.uniquekey
, b.metric1
, b.metric2
, b.metric3
FROM ININ.dbo.table1 a
LEFT JOIN LinkedServer.ININ.dbo.table2 b ON a.uniquekey = b.uniquekey
WHERE b.uniquekey IN (SELECT uniquekey FROM ININ.dbo.table1);

I also loaded both tables and did a Merge query w/an Inner Join to a new table.  I can't find a way to systematically drop the unneeded when it's finished.  It's very slow, too.

Is there a way to filter the PBI load based on a field/values in a table earlier in the (same) load?  If not, is there some M code or something to filter the huge table down and drop the prefiltered table?

Thanks in advance for any/all  help.

[V-lianl-msft]

Hi @jcampbell474 ,

When connecting data, you can use SQL query in advanced options to preprocess data. Have you tried this?

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

[richbenmintz]

Well the first question i have, is why query the linked table and inner join to the non linked table, that will remove the need for the 'IN' condition.

The second thing to try would be to create two tables, 

table1 and table2, then create a filter step in table table2 that takes a list from table1, see the thread below

https://community.powerbi.com/t5/Desktop/Pass-a-list-as-filter-argument-in-Power-Query/td-p/149101

[jcampbell474]

Can't do an Inner Join because it's not a 1:1.  There will always be more records in Table1.  Just grabbing records from Table2 where there's a match.

I'll try the approach listed in the link.  Looks like it will still result in all the data being loaded and filtered. 

Thank you for the reply.