Direct Query - Queries using CTE's are not being accepted

[Anonymous]

Hi, I have a Query that runs T-SQL code with 3 CTEs as part of the code. 
It's in import mode and works great with no issues 
But when creating a new direct query and using this code, I get the following error.

Microsoft SQL: Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ','. Incorrect syntax near ','. Incorrect syntax near ')'.

Question, are CTE's allowed in Direct queries? This works without errors, until i run it as a direct query. And I tried replacing the commas after each WITH statement, and of coures as expected, it won't run unless they're commas.

[Anonymous · Accepted solution]

Hi @Anonymous 

I'm afraid the answer is No. As of now, CTEs still don’t work in DirectQuery mode. I find the following description from official document Data transformation limitations of DirectQuery. 

Also, the Get Data dialog or Power Query Editor use subselects within the queries they generate and send to retrieve data for a visual. Queries defined in Power Query Editor must be valid within this context. In particular, it's not possible to use a query with common table expressions, nor one that invokes stored procedures.

As a workaround, consider building your data sets in SQL Server using views and then connecting directly to those views in Power BI. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Guess]

I am receiving an error, and in my case even import doesn't work. It says syntax error around ";". The query runs well when in dbeaver

[Anonymous · Accepted solution]

Thanks very much for the feedback and the specifics. I guess an answer, even if it isn't the one I was hoping for, is better than not knowing.
I'll make changes to the queries with the CTE's.

[Anonymous · Accepted solution]

Hi @Anonymous 

I'm afraid the answer is No. As of now, CTEs still don’t work in DirectQuery mode. I find the following description from official document Data transformation limitations of DirectQuery. 

Also, the Get Data dialog or Power Query Editor use subselects within the queries they generate and send to retrieve data for a visual. Queries defined in Power Query Editor must be valid within this context. In particular, it's not possible to use a query with common table expressions, nor one that invokes stored procedures.

As a workaround, consider building your data sets in SQL Server using views and then connecting directly to those views in Power BI. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[zbWorkProfile]

Do we know if that's slated to change anytime soon? A big reason to use CTE is needing to create calculated columns which would then be referenced in the actual query; converting CTE to subqueries blows up quickly in that scenario. Needing to push the build back to a SQL Server view just to be able to DirectQuery that data is both (1) unnecessary extra steps, and (2) extraneous and unnecessary internal resource diversion.