Power Query to Filter a SQL view based on an Excel column list

1
votes

Is there a way to filter a SQL view based on a list of values in an excel table column using Power Query?

I have a SQL view that returns a large set of data (millions of records or properties). Users want to filter that based on an excel table column of property IDs. I know I can just do a merge join based on the property ID between the view and the excel column in power query. But it looks like the merge brings in the millions of records first then filtered it in the join. Which takes a long time. Users want to change the list of propertyIDs on the fly on a daily basis and run the query.

Essentially, I wanted to create in Excel power query what is in SQL

SELECT * FROM SQLViewName 
WHERE PropertyID IN (Select Column from ExcelTable)
1
sql-serverexcelpowerbipowerquerym

1 Answers

3
votes

You should be able to do this with a List.Contains function.

If my ExcelTable is

ID
---
436
437
438
439

then adding a filter like this should do the trick:

Table.SelectRows(SQLViewName, each List.Contains(ExcelTable[ID], [PropertyID]))

When I tried this and did View Native Query on the last applied step, it folded the Excel table into a WHERE clause with the ExcelTable values as literals like this:

select [_].[PropertyID],
    [_].[OtherColumns]
from [dbo].[SQLViewName] as [_]
where [_].[PropertyID] in (436, 437, 438, 439)

This allowed me to load a multi-million-row table in just a couple seconds.