Forum Discussion
Custom query with CTE not supported (bug?)
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.
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
Any update on this issue? Still a problem over a year and a half later.
What happened with this? Would like an update from over a year and half ago
Is there any fix to this issue till date?
v-qiuyu-msft Was there ever a response/feedback internally on this?
I don't think offically. I was able to work around it with making the query into a view and then selecting from there.
