Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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.

FrankKrogh_0-1693319134167.png

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

1 ACCEPTED SOLUTION
cpwebb
Employee
Employee

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

 

View solution in original post

7 REPLIES 7
cpwebb
Employee
Employee

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

 

Anonymous
Not applicable

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! 🙂

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

Confirmed. The workaround works.

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

foodd
Super User
Super User

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?

Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors