Forum Discussion
User input for dynamic SQL Queries in Power BI desktop?
Hello Yolo,
Thanks for your information. Somehow it did not work for me. I am using Power BI Desktop May 2021, if that makes a difference.
Here were what I did.
- Created a new Power BI file.
- Selected SQL Server as data source and selected Direct Query.
- Entered the SQL select statement in the query dialog.
- Went to the query editor to create a parameter.
- Edited the query in the Advanced Editor, which appears to have converted the regular SQL statement to the M language. I was able modify it to use the parameter.
- Went back to Power BI desktop to create a table under the Modeling ribbon. But the New Table option is greyed out. Therefore it appears it does not work for me.
Best regards
Hi Andy__
After testing, if your connection mode is direct query, the create new table can be used, try to clear permission and reconnect the sql again.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Yolo,
I created a new file to do the experiement, and I did not save it. Therefore I cannot clear the permission to check whether it works or not. However, when I went back to the existing file, I found that the create new table option is available.
However, the "Bind to parameter" option is not available!
When I looked around and when I looked back at one of your reference links, Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
I found under "considerations and limitations" that:
"
- Dynamic M query parameters are not supported on Power BI Report Server.
"
And we are using Power BI Report Server.
Best regards