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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michaeljc70
Advocate II
Advocate II

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 

26 REPLIES 26
PetrSors
New Member

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

Anonymous
Not applicable

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
Frequent Visitor

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

michaeljc70
Advocate II
Advocate II

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.

 

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

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

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

 

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

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

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.  

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

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.  

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

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

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

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

HI1
Frequent Visitor

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!

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
Not applicable

Did you use Import or Direct Query?

 

Thanks

 

B

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. 

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

 

FYI, most people start these conversations with "thanks for trying to help me ..."

 

Writing custom SQL query generally works just fine - it's just recommended that you don't do it that way, that you create a view. Along those lines self-service BI doesn't mean that people are going to write SQL queries with CTEs to access the data, so I don't see not being able to do that in the tool as a major limitation to self-service BI.  The only limitation that you have here is that you need a view created in your database, and that's not a limitation of this product.

 

To get around the limitation of creating a view, this solution does allow you (as a self-service tool) to take the tables out of your database and do the joining yourself using the query editor GUI.  Your data must be very, very large if you can't get that to work in Power BI, and if your data is that large I'll ask you again, why aren't you doing that in a view in your database?  If you have a nail that you need to put in a wall and you reach in your bag and get a screwdriver, well I don't think that's the screwdriver's fault.

 

One last point, I realize you're not trying to "test your skills" but I will say that this product is not BI magic dust - it does require some skills to use. If you're the type of person that can write a SQL query with a CTE then you'll be able to figure it out.  Unless you don't want to figure it out.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.