Query with CTE gives error

[michaeljc70]

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 

[djaxkappa]

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. 

[PetrSors]

Just an update, that while this issue has been fixed for Import connectivity mode, it still does not work for DirectQuery connectivity mode.

[Anonymous]

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

[rowlandshaw]

The detail I'd add here, is that the query preview works, it's when evaluating the query that it falls over

[michaeljc70]

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

[austinsense]

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.

[michaeljc70]

I wrote a query that uses a CTE. I don't have permissions to create a view or stored procedure. 

[austinsense]

Gotcha, putting a custom sql query into the query builder is not recommended - as you can see

Here are your options ...

1. Create a view - sounds like this isn't an option for you

2. Re-write the query without using a CTE - that would be a good test of your SQL skills

3. Stop using the custom sql code, query the underlying tables using the GUI, and use PQ to create the output you want - that would be a good test of your PQ skills

[michaeljc70]

I am not sure why it isn't recommended. I don't see the documentation saying that and I don't see a list of query limitiations.   

I am evaluating Power BI to determine in my organization wants to buy and use it as a visualization tool.  I am not trying to "test my skills".   I am trying to evaluate products to see how they work (or if they work in this case).  

I cannot import the tables and join them because they are too large. I cannot use Direct Query because it doesn't support many to many relationships.  All I see are a lot of llmitations.

Power BI is supposed to be self-service.  That typically means not writing views or stored procedures (and generally not CTEs) and if a feature is there, I expect it to work.  

[rbbi]

your issue and thoughts about it are exactly what I'm facing today. Would be nice if it jsut passed through the query and used the results like it does in the preview. My query also works fine in PowerPivot with a recursive CTE and I was just trying it in Power BI but the same code in Power BI isn't accepted. cheers

[JimLuo]

I have the same issue with CTE. It will be much convenient to support CTE.   I use a workaround to save data into excel and import it.  

[Mandr0id]

Same issue with CTE... Someone got it working?

[geoffwill]

Having the same issue with CTEs, however was able to re-write to use a subquery and it works

[Nobby]

It's a shame. I also have this problem. I've removed the CTE but the code is much less readable/maintainable. Ho hum.

[jimjao]

Hi all,

Using Power BI Desktop (October 2017), I was able to use CTE query with an OLE DB connection to the SQL Server.

So, Power BI does support CTE.

Jim Jao

[Anonymous]

I can confirm this works with OLE DB in PowerBI Desktop, but unfortunately scheduling an online refresh still seems to be causing problems, so the only way it works is republishing from locally updated results every time, which is still a very inconvenient workaround!

[MasterCMU]

Yes, this is still correct in Power BI Desktop November 2019 - confirmed it works ONLY through a OLE DB connection to SQL rather than through the SQL connection in Power BI.  I came across the issue with CTE at the final "Apply" stage of Power Query with a Direct Connection and while the preview was working, the Apply and Close would fail with the same message as OP's. Using JimJao's fix with Ole DB I got it to Apply and Update. So it seems to be a bug in the SQL connector.

[Anonymous]

Did you use Import or Direct Query?

Thanks

B

[roselilly23]

I was getting the issue when using Direct Query with custom sql containing CTEs. I just re-wrote the query to have sub queries rather than the CTEs and it works fine now. 

[jimjao]

Hi all,

Using Power BI Desktop (October 2017), I was able to use CTE query along with an OLE DB connection to the SQL Server.

This is no longer truth.

Jim Jao