Direct query syntax returns data in PowerQuery perfectly but shows error when update PowerBI project

[FrankKrogh]

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? 🤔

[cpwebb · Accepted solution]

I wrote a blog post about this:

https://blog.crossjoin.co.uk/2022/03/06/why-not-all-sql-queries-can-be-used-as-a-source-in-power-bi-...

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-powe...

[FrankKrogh]

Excellent solution! I have tried many approaches and almost given up. Today I finally managed to finish my PowerBI views. It really made my day! 🙂

[foodd]

Nothing like getting the solution from the one and only source  🙂  Sign up for his Blog update if you have not already.

[Martin_D]

Confirmed. The workaround works.

[foodd]

Thank you Chris for taking the time to help with this.

[foodd]

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?

[FrankKrogh]

I am sorry that I forgot to mention that I am saving the project in PowerBI Desktop.