Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
Creating views instead of CTE is not always an option.
Need either CTE ot Temp tables support in PowerBI when creating data sets.
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
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
I don't think offically. I was able to work around it with making the query into a view and then selecting from there.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |