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
jsBrizius
Helper I
Helper I

Custom query with CTE not supported (bug?)

Is creating a custom query using a CTE officially not supported or am I just duing it wrong?  It works in Query Editor, but you cannot close and apply.  I suspect it is becuse the query seems to be embeded into another select when you try to run the close and apply.

1 ACCEPTED SOLUTION
WESTi
Helper I
Helper I

I ran into this issue when attempting to use Direct Query Mode. As Qiuyun has said, you can use import mode as a way around this but then you obviously have the timing issues with the data set being dependent upon being refreshed (not live).

 

I ended up having to build most of my data sets in SQL server using views etc and then direct connect to those.

View solution in original post

27 REPLIES 27

Just wanted to bump this topic.

 

Please fix this!!!

 

I use CTE's quite often in my SQL, and re-writing my queries so Power BI will "like" them both adds another step and throws a wrench into my query formatting.

 

Not to mention how frustrating it is to see your query evaluate, get a preview of your data, and then on the second query evaluation be told that incorrect syntax has been detected.

 

Thank you

v-qiuyu-msft
Community Support
Community Support

Hi @jsBrizius,

 

I have reproduced the issue in DirectMode. When use With CTE clause, Power BI Desktop will treat this clause as a derived table, then cause syntax error. We can use SQL Profiler to trace executed command. See:

 

z1.PNG

 

a2.PNGa3.PNGa4.PNG

 

I will report it internally and will give you update once I get any feedback. Currently, you can use Import mode instead of DirectQuery mode to workaround the issue temporarily.

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-qiuyu-msft  Was there ever a response/feedback internally on this?

Anonymous
Not applicable

I don't think offically. I was able to work around it with making the query into a view and then selecting from there.

Anonymous
Not applicable

Is there any fix to this issue till date?

What happened with this? Would like an update from over a year and half ago

Any update on this issue? Still a problem over a year and a half later.

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.