Incorporate a local data set in Excel Power Query against SQL Server

Question

Business Case:
I have a list of key IDs in an excel spreadsheet. I want to use Power Query to join these IDs with a details table in a SQL Server database.

Problem
Currently using Power Query I only know how to import the entire table, which is greater than 1 million records, then do a left join on it against an existing query that targets a local table of IDs.

What I want to do is send that set of IDs in the original query so I'm not pulling back the entire table and then filtering it.

Question
Is there an example of placing an IN clause targeting a local table similar to what is shown below?

= Sql.Database("SQLServer001", "SQLDatabase001", 
[Query="SELECT * FROM DTree WHERE ParentID 
IN(Excel.CurrentWorkbook(){[Name="tbl_IDs"]}[Content])"])

Power Query will not bring back the entire database table if you just create a connection to the SQL Server table. Then it's as simple as the Merge Tables button. Left Outer is the default. I do this with connected Oracle tables with millions of rows all the time. — tbur

Mike Honey Mike Honey · Accepted Answer · 2017-10-10T04:50:03

I would first build a "Connection only" Query on the excel spreadsheet key IDs.

Then I would start a new Query by connecting to the SQL table. In that query I would add a Merge step to apply the key IDs query as an Inner Join (filter).

This will download the 1m rows to apply the filter, but it is surprisingly quick as this is mostly done in memory. It will only write the filtered result to an Excel table.

To improve performance, filter the rows and columns as much as you can before the Merge step.

Incorporate a local data set in Excel Power Query against SQL Server

1 Answers