Forum Discussion
Solved
Value.Native Query and CTE
bbrowne ImkeF AlexisOlson
I am querying a table from sql server (lakehouse) and I am noticing that Value.NativeQuery generates and error
E.g.
//fn1
let
sql = (server as text, db a...
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.
I wholeheartedly agree. I heavily rely on CTEs as well and am running into issues too when trying to get incrememental refresh to work at the PBI report level because the M queries do not fold. What ensues is the creation of multiple reports, duplicate of each other, but each refreshing against a different calendar year. Very suboptimal.