Value.NativeQuery bypass Query Folding?

[learnuse]

Hello,

When the M function Value.NativeQuery is used, does that mean Query Folding is bypassed for that specific transformation step? I’m not referring to the steps that follow this transformation step, but specifically to the step involving Value.NativeQuery.

Based on various articles, it seems that during this transformation step with Value.NativeQuery, a developer writes a native query itself (e.g., T-SQL), indicating that Query Folding - translating to the data source's native query - is unnecessary. While the "View Native Query" option (in Applied Steps) remains enabled for that transformation step since a native query is defined by the developer, it appears that Query Folding does not occur for this specific transformation step.

Could you please clarify?
a) Is Query Folding bypassed for the specific transformation step with Value.NativeQuery?
b) What exactly happens during the transformation step with Value.NativeQuery?

In all following examples, there is the "View Native Query" option enabled for the last step in Applied Steps.
1)
let
Source = Sql.Database(Server, "ADI_EMEA_DMT", [CreateNavigationProperties=false]),
UseNativeQuery = Value.NativeQuery(
Source,
"select Column_ID from SQL_Server_Data",
null,
[EnableFolding = true])
in
UseNativeQuery

2)
let
Source = Sql.Database(Server, "ADI_EMEA_DMT", [CreateNavigationProperties=false]),
UseNativeQuery = Value.NativeQuery(
Source,
"select Column_ID from SQL_Server_Data",
null,
[EnableFolding = false])
in
UseNativeQuery

3)
let
Source = Sql.Database(Server, "ADI_EMEA_DMT", [CreateNavigationProperties=false]),
UseNativeQuery = Value.NativeQuery(
Source,
"select Column_ID from SQL_Server_Data")
in
UseNativeQuery

[learnuse]

@Anonymous
Thank you for your confirmation that Query Folding does not occur for the transformation step with Value.NativeQuery since the query included in Value.NativeQuery already is a native query. This really wasn't explicitelly stated anywhere, however there were indications that is how it works.

Note:
I was confused when I heart or read that if the "View Native Query" option (in Applied Steps) remains enabled then the Query Folding occurs for that transformation step, which is not true in case of M function Value.NativeQuery as you clarified.

[Anonymous · Accepted solution]

Value.NativeQuery is a query written in the source's native language, which is what query folding does with M code. There is no need for query folding on a NativeQuery, because it already is a Native Query.

--Nate

[lbendlin]

Please follow the documentation

Query folding on native queries - Power Query | Microsoft Learn

[learnuse]

There is written: This article showcases an alternative method to create native queries against your data source using the Value.NativeQuery function and keep the query folding mechanism active for subsequent steps of your query.

This seems to be the closest answer to my question. Although it mentions that users can write native queries, it doesn’t explicitly state that Query Folding does not occur for the transformation step with Query Folding. I’ve encountered situations before where I assumed one thing, but the reality was different. Therefore, I want to be certain I understand this correctly.

If I’m mistaken, could you please provide the exact text that explicitly confirms Query Folding does not occur during the transformation step when using the Value.NativeQuery function?

[lbendlin]

You are missing the bigger picture.  Native Queries fold by definition, but they also prevent further folding in subsequent steps.  UNLESS you indicate that they support it.

[learnuse]

Thank you for your time regarding this topi.

My understanding follows.

Query Folding means combining or "folding" multiple steps in a given query into a single operation (source: https://www.dremio.com/wiki/query-folding/). That's it, nothing else. In other words, when we say Query Folding or only folding, we mean taking Power Query transformation steps (or M Script / M Code) and creating a single query that is the native query for the data source. Note: There is nothing regarding sending to the data source when we talk about the pure Query Folding.

As discussed with @Anonymous, why should Power Query Mashup Engine create a native query from an already prepared native query?

You wrote "Native Queries fold by definition" which means, take a native query and fold it, meaning create a new native query from that taken native query. That does not make a sense to me. I doubt that Power Query Mashup Engine would be so ineffective, but I wanted to be sure, therefore I created this topic.

[lbendlin]

Please read the documentation again. It clearly spells out the mechanics.

Query folding on native queries - Power Query | Microsoft Learn