Forum Discussion

jsBrizius's avatar
jsBrizius
Helper I
10 years ago
Solved

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 e...
Need Help
  • WESTi's avatar
    WESTi
    10 years ago

    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.

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
10 years ago

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

 

 

preese's avatar
preese
New Member
8 years ago

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