Query folding

[ahmedshalabyy12]

Dears,

How i can verify that my query is folded 

that's the query :

let
// Define your start and end range
RangeStart = #datetime(2025, 8, 1, 0, 0, 0),
RangeEnd = #datetime(2025, 8, 10, 0, 0, 0),

// Convert to strings in correct SQL format (ISO)
StartText = "'" & DateTime.ToText(RangeStart, "yyyy-MM-dd HH:mm:ss") & "'",
EndText = "'" & DateTime.ToText(RangeEnd, "yyyy-MM-dd HH:mm:ss") & "'",

// Build SQL query with literal values
SqlQuery = "SELECT * FROM golden.ft_chat WHERE datetime >= " & StartText & " AND datetime < " & EndText,

// Execute query
Source = Value.NativeQuery(
Databricks.Catalogs(
#"Server Host Name",
#"HTTP Path",
[Catalog=#"Default Catalog"]
){[Name=#"Default Catalog", Kind="Database"]}[Data],
SqlQuery,
null,
[EnableFolding = true]
)
in
Source 

[Shubham_rai955]

You can verify by doing below steps:

• In Power Query, go to View -> Query Dependencies to see if steps stay connected to the source.

• Right-click your last step and check if View Native Query is enabled, if clickable, folding is happening.

• Disable a step and re-check to confirm where folding stops.

• Ensure transformations are supported by the Databricks connector for folding.

[Madalina2801]

Hi,

I'm facing the same issue, not sure if query folding works or not. I can see in query history the query runs in a few seconds but the incremental refresh is taking a lot (almost two hours).

Is this a known issue related to the Databricks connector?

[mmarcoux]

Same issue, using the databricks connector, shows support for folding in UI, but incremental refresh says the query is not fully foldable. Disabling full folding in the incremental refresh setting in dataflow gen2 makes the run times go from 30m to 5hrs, at least for my workloads.

[Madalina2801]

I managed to reduce the refresh duration from 2h to 20m after optimizing the dataset (I removed some high cardinality columns). In SSMS I saw the partitions were processed in 10m but the total refresh duration was 2h. It turned out that the problem was slow Power BI post-load processing. Even if the query runs fast on the source side in Databricks, the VertiPaq often recompresses column dictionaries across all partitions in the table, especially for high-cardinality or long text columns.

[ahmedshalabyy12]

can you explain what do you mean by incremental refresh taking alot of time ? 

[rohit1991 · Accepted solution]

Hi @ahmedshalabyy12 

How to verify your query is folded (Databricks + Value.NativeQuery):

1. Value.NativeQuery already sends SQL to Databricks. Folding only matters for steps after that step.

2. In Power Query, right-click the last step >> View Native Query.

   • Clickable = still folded to SQL.

   • Greyed out = folding broke earlier.

3. Do a quick litmus test: add Keep Top Rows >> 5 (or filter a date). Recheck View Native Query.

   • If you see LIMIT 5 (or your WHERE clause) in the SQL, it’s folded.

4. Cross-check in Databricks >> SQL >> Query History while preview/refresh runs.

   • Your server log should show the final SQL with your filters. If it shows SELECT * and filters aren’t there, they were applied locally.

5. Remember: many steps break folding (e.g., Table.Buffer, custom M, merges with non-foldable sources). Put them after you’ve narrowed data.

[ahmedshalabyy12]

I filtered on top 5 rows it still grey 

but when i searched for history query i found the query running in databricks 

so what that mean view native query is grey but the query is running in databricks from query history 

[rohit1991]

Hi @ahmedshalabyy12 

View Native Query being grey doesn’t mean folding is off. With Databricks, that button often stays disabled even when Power Query pushes your steps (like Top 5) down to the source. The fact you see the query in Databricks Query History means folding happened and Databricks executed it.

Quick ways to verify:

• Turn on Query folding indicators (Power Query Options). Solid/striped icons = folded.

• Make your filters/projections the first steps; refresh preview and watch Query History (you should see LIMIT 5/TOP 5).

• If you add a non-foldable step later (e.g., Table.Buffer, some custom columns/merges), the button greys out even though earlier steps still folded.