Forum Discussion
Direct query syntax returns data in PowerQuery perfectly but shows error when update PowerBI project
When I run this Direct query syntax (calling a stored procedure in SQL Server) it returns data in PowerQuery perfectly without error and displays the returned data in the table.
However when I when try to save my PowerBI Desktop project I receive an error message that does not make much sence to me:
Microsoft SQL: Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'.
So how can there be an error when the data from the query already are returned and displayed in PowerQuery? 🤔
I wrote a blog post about this:
Basically, if you can't use your SQL query as a subquery then you can't use it as a source in DirectQuery mode. The only workaround I'm aware of is this: https://prologika.com/power-bi-directquery-with-parameterized-stored-procedure/
You can use a table valued function as a source in DirectQuery mode though: https://blog.crossjoin.co.uk/2022/02/20/tsql-table-valued-functions-and-dynamic-m-parameters-in-power-bi-directquery/
7 Replies
I wrote a blog post about this:
Basically, if you can't use your SQL query as a subquery then you can't use it as a source in DirectQuery mode. The only workaround I'm aware of is this: https://prologika.com/power-bi-directquery-with-parameterized-stored-procedure/
You can use a table valued function as a source in DirectQuery mode though: https://blog.crossjoin.co.uk/2022/02/20/tsql-table-valued-functions-and-dynamic-m-parameters-in-power-bi-directquery/
Confirmed. The workaround works.
Nothing like getting the solution from the one and only source 🙂 Sign up for his Blog update if you have not already.
Thank you Chris for taking the time to help with this.
When you save "save project", are you meaning 'publish to Service'? Is this SQL Server on-prem? Is there a Gateway between upstream and downstream?
