Dynamic M Query Parameters - Custom Value

[rnola16]

Hi,

SQL:

Select

A.EMP_ID, A.NAME

From EMP A

where A.EMP_ID = 'Parameter1'

Here the EMP table has got billion records. I have a direct query with the custom SQL included and tried to parameterize the query on EMP_ID. How can we use dynamic M query parameters on EMP_ID so users can input their choice of 'EMP_ID' in the parameters. ?

If the table was small I would inlude all values and define on a new table and bind that to parameter but how do you work with huge tables ?

Thanks.

[Deku · Accepted solution]

if the users already know the id they can pass arbitrary values without having to define a lookup list

.https://blog.crossjoin.co.uk/2023/01/29/passing-any-arbitrary-value-from-a-power-bi-report-to-a-dyna... 

[rnola16]

I owe you a lunch. Thanks.

[Akash_Varuna]

Hi @rnola16 Could you try this please for Dynamic Query Parametrs
Create a parameter (`Selected_EMP_ID`) and reference it in the SQL query within Power Query to filter dynamically. Use a text box or slicer in the report for user input. Ensure query folding works and the `EMP` table has an index on `EMP_ID` for performance.
Please Check the following power query

let
    Source = Sql.Database("YourServer", "YourDatabase", [Query = "SELECT A.EMP_ID, A.NAME FROM EMP A WHERE A.EMP_ID = '" & Selected_EMP_ID & "'"])
in
    Source

[rnola16]

This was my initial try, but here the query would do two iterations. 1. Bring all the EMP_ID 2. Run for the EMP_ID selected. Not efficient. 

Thanks.