Filtering a sql table on a list of values before import

[Anonymous]

Hi all, 

I have a > 30 millions records SQL table (stored in an Azure db). I would like to import only the 10k records whose ID can be found in another table that is already in my Power Bi data model. I import the external table with a query from the Transform Data menu:

my_external_table= Sql.Database(parameter_servername, parameter_dbname, [Query=" SELECT record_ID, text FROM external_table WHERE record_ID IN (...) " ])

I do not know how to properly write that WHERE clause. Is there a way to pass the column of my power bi table in the query? Like, let's say, saving it in a container of some sort?

In pure SQL a simple JOIN with the external table on the IDs of my power bi table would be enough, but I do not know how to achieve what I want without having to import all 30 millions records first.

EDIT: I also thought of creating a parameter with a list of values, the list being the whole column of my record_IDs. This way I should be abke to use it as argument for the IN clause. Still, if I go to Manage parameters -> New parameter -> Query the table where the list of IDs is does not show up in the dropdown menu. Of all the data model, only 4 tables appear.

[amitchandak]

@Anonymous , You can write a choice of the query in Advance Property option, if it one time or refer

https://community.powerbi.com/t5/Desktop/parameterize-connection/m-p/205900#M90712

https://www.biinsight.com/power-bi-desktop-query-parameters-part-1/

[Anonymous]

hi @amitchandak , thanks for your help.

I am not sure I understand: if I create a parameter out of my list, I still need to assign it a value and, when I execute the query, the parameter has that value and will filter based on that value only. Am I getting something wrong?

In the advanced properties of the query, how should i write my query to include a list of values that is already in the data model?

edit: let me add that my list is 10k items long, so I cannot add it manually. I followed the procedure in one of the articles you linked (right click on the column -> add as new query -> use it to create a parameter) but I still have the problem that then I have to assign a single value to the parameter, so I cannot use it in the query.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try a custom function and invoke it with a column that exists.

M code may like this:

(Year as number)as table=>
let 
    Source = Sql.Database("******", "test", [Query="SELECT *  FROM test_11#(lf) WHERE Year IN ("&Number.ToText(Year)&")", CreateNavigationProperties=false])
in
    Source

When you invoke the query you will get a prompt asking for permission to run a native database query. This is a caution to warn you that some SQL is being run which could makes changes to the database.

This request for permission can be turned off. Click on File and then Options, and then Security.

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