You can work around it with a sub-query (just move the CTE in to your main query as a sub-query), which is crude and very SQL2K'ish but that is the world we live in, apparently.
@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.
So, it seems CTEs are only supported in Import mode and not in Direct Query. Still the case?
To be fair I have not tested recently. But I have not seen an update on the matter from Microsoft. I will test again when I have some time and post and update here.
Why is this issue marked "Solved"? Power BI still throws an error when closing the Query Editor for any query containing a CTE (which seems pretty insane given the amount of time this has been a known issue).
Confirming this is still an issue 5 months from last post for anyone coming across this thread!
Agreed. The workaround given earlier in this thread creates a table that is in import mode, so no improvement on using import mode in the first place that I can see.
Read the announcement for more information!
We're rolling out new Kudos Given badges. Find out how many Kudos you've given.
Get an overview of the events and great community content from November.
Find out where you can attend!