Forum Discussion
Query with CTE gives error
I used SQL Server and a query that uses a CTE. It gives me an error "Incorrent syntax near the Keyword 'WITH'. ......."if this is a common table expression......previous statement must be terminated witha semicolon."
The query runs fine outside of Power BI. I tried putting a semi-colon before the 'WITH', but that didn't work. I see the data in the grid, but can't apply query changes and get the error
27 Replies
I agree with the poster. I also came across the issue with CTE at the final "Apply" stage of Power Query. And I had spent tons of time investigating what's the casue for it and finally got chance to read this post, which really does not make sense to me that it works out in the PowerQuery preview section but not working at the final applying section...
Yea we could definitely rewrite the query without using CTE, but just as a user persective, this is just not making sense and I would definitely think it's the limitation of the SQL compiling in PowerBI
The detail I'd add here, is that the query preview works, it's when evaluating the query that it falls over
One more thing...though I can see my data under "Edit Queries", I cannot create a visualization because the query doesn't appear under "Fields".
Are you accessing a view that uses a CTE or did you write a SQL query that uses a CTE?
If it's the latter, then I'd suggest creating a view and then querying the view.
I wrote a query that uses a CTE. I don't have permissions to create a view or stored procedure.
Just an update, that while this issue has been fixed for Import connectivity mode, it still does not work for DirectQuery connectivity mode.
I was able to get around this in the Snowflake Connector and get my CTE to work by editing the default Source function. It looks like it automatically sets a variable at the end that when I set to false it works.
= Value.NativeQuery(Snowflake.Databases("SERVER","WAREHOUSE"){[Name="DB"]}[Data], "YOUR QUERY HERE", null, [EnableFolding=true])
Try changing the last parameter to [EnableFolding=false]. This worked for me both getting the preview and the load to work. Strangely, at least one of my CTEs still worked with EnableFolding=true.
