Forum Discussion
Value.Native Query and CTE
I just read about this issue on Reddit the other day:
CTEs don't really work with query folding because they are in-memory tables and don't typically work when treated as a sub-query which folding attempts to do.
Need guidance on Value.NativeQuery() limitation when using SQL CTEs, and why these CTE expressions don't appear to work when folding is enabled? : r/PowerBI (reddit.com)
It sounds like you might not be able to get both query folding and CTEs working at the same time.
MS must do something about it by adding support for CTEs/temp table/table variable.
HI smpa01,
In fact, this type of operations already been limited to use in fabric.
You can't directly create in-memory temporary tables at the query data operations. In my opinion, I'd like to suggest you split these to multiple steps and create fact data to instead temporary table.(you can remove them after processing)
In addition, you can also submit idea for these types of operations.
Regards,
Xiaoxin Sheng