Power Query: Filter/SQL Select on an array/table of values

[eljetfan]

I have taught myself enough to write some basic SQL Statements. I want to use PowerQuery to extract data on call it 1,000 records (table.lead_id) from a list of 1,000,000+ records. In other words, give me call records on specifically lead_id = 10001111, 1000125, 1000145, etc. where the select list of lead_id values is large and dynamic).

I guess there are three different ways to handle this. #1 is something that I know how to do but it doesn't seem like the right approach.

1) Write a massive SQL statement

i.e. select[..] where (lead_id=10001111 or lead_id=1000125 or lead_id=1000145, or, or, etc, etc)

2) Reference an array of lead_ids in a SQL select statement

select [..] where lead_id is contained in array of lead_id values

3) Reference an array in a filter in Power Query

filter where lead_id is contained in an array of lead_id values

For 2 & 3, the arrays would be driven from values somewhere else in Excel. (Different workbook or same workbook)

I think approaches 2 & 3 are the most elegant but I do not have an idea on how to do them.

I couldn't get anywhere doing a Google search.

Any suggestions?

Thanks

 

[ImkeF]

This should normally be piece of cake using the 3rd option: Merge/join your filter table (query) with the SQL-source on JoinKind.Inner and the command should be folded back to the server, enabling the filtering to be done there and the results being returned fast to PQ.

But there is (has been?) a bug that disables folding when your table is larger than 200 rows: Filter SQL-server queries with Excel-tables: Query folding limitations and bug workaround – The BIccountant

Also to my knowledge it is not possible to use your option2 without passing everything through text-fields. So you cannot send an Inner.Join-statement for example, that references a query from PowerQuery. (Only thing you can do here is to let Power Query automatically create a (very long) statement with all your filter-arguments, using Expression.Evaluate like described here: Select rows that have no empty fields using Expression.Evaluate in Power BI and Power Query – The BIccountant)

But in general it is possible to edit your SLQ-statements dynamically: Pass Parameters to SQL Queries - The Ken Puls (Excelguru) BlogThe Ken Puls (Excelguru) Blog

So I'd recommend starting with the merge with JoinKind.Inner and check if you can live with the performance