Custom query with CTE not supported (bug?)

[jsBrizius]

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.

[Mandr0id]

Creating views instead of CTE is not always an option.

Need either CTE ot Temp tables support in PowerBI when creating data sets.

[Vlasz]

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]

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:

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

[adrez86]

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

[Anonymous]

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]

Is there any fix to this issue till date?

[adrez86]

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

[preese]

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