Forum Discussion
Dynamic M Query Parameters - Custom Value
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.
if the users already know the id they can pass arbitrary values without having to define a lookup list
4 Replies
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 querylet Source = Sql.Database("YourServer", "YourDatabase", [Query = "SELECT A.EMP_ID, A.NAME FROM EMP A WHERE A.EMP_ID = '" & Selected_EMP_ID & "'"]) in SourceThis 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.
if the users already know the id they can pass arbitrary values without having to define a lookup list
I owe you a lunch. Thanks.
