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.