Diagnosing & Fixing Broken Query Folding in Power Query

This lesson on Diagnosing & Fixing Broken Query Folding in Power Query is hands-on and example-driven. You will diagnose and verify query folding across relational databases and non-relational APIs using native query checks, external profiler traces, and built-in Power Query Diagnostics. You will identify whether data transformations execute on source database engines or fall back to local mashup engine processing.

What You'll Be Able To Do

• Inspect applied steps in Power Query to view generated native SQL statements.
• Identify transformations that break native translation due to source engine function limitations.
• Capture and analyze SQL Server trace events in Azure Data Studio during preview refreshes.
• Run Query Diagnostics to evaluate server-side parameter pushdown on OData feeds.
• Filter diagnostic output tables by specific evaluation operations to verify server pushdown.

Detailed Concept Walkthrough

1. Query Folding and Source Translation

Query folding offloads data transformation logic from the Power Query mashup engine directly to the underlying data source. It translates M steps into the source system's native language, such as T-SQL.

• Mechanism: When a transformation step is applied, Power Query evaluates whether the operation maps directly to an equivalent native function in the source data engine.
• Under the Hood: Native transformations like absolute value calculations execute within source database memory, significantly reducing the volume of data transferred across the network.
• Execution Flow: Unsupported transformations, such as text capitalization, force the mashup engine to halt folding, pull all upstream rows locally, and process downstream steps in local memory.

// Example M Query Step Translation
// Folded Step: Translated to T-SQL ABS([AccountBalance])
= Table.TransformColumns(#"PreviousStep", {{"AccountBalance", Number.Abs, type number}})

// Non-Folded Step: Cannot translate Text.Proper to T-SQL; halts folding
= Table.TransformColumns(#"PreviousStep", {{"City", Text.Proper, type text}})

Key Takeaway: Query folding dramatically improves performance by executing operations inside the source database rather than the local Power Query engine.

2. Native Query Inspection in Applied Steps

The View Native Query context menu option provides an immediate UI check for translated native SQL statements. It displays the cumulative SQL query generated up to the selected step.

• Mechanism: Right-clicking any step in the Applied Steps pane exposes the View Native Query option to view the generated SQL statement sent to the source.
• Best Practice: Check applied steps sequentially from top to bottom to isolate the exact step that causes the View Native Query menu option to become grayed out.
• Under the Hood: A disabled View Native Query button indicates that the Power Query UI cannot display the query plan, but it does not definitively prove folding stopped at runtime.

-- Generated T-SQL inspected via 'View Native Query'
SELECT 
    [CustomerID],
    [City],
    ABS([AccountBalance]) AS [AccountBalance]
FROM [dbo].[Accounts];

Key Takeaway: View Native Query provides quick feedback, but a grayed-out button can often be a false negative indicator.

3. External Profiler Tracing in Azure Data Studio

External profiler traces capture the actual SQL queries received by the source database in real time. They provide definitive verification when Power Query UI indicators are ambiguous.

• Mechanism: Launch an active profiler trace session in Azure Data Studio or SQL Server Profiler targeting the source database before refreshing data.
• Execution Flow: Triggering a data preview refresh in Power Query sends outbound queries to the source, which are recorded as SQL batch events in the profiler.
• Under the Hood: Profiler traces reveal that operations like keeping top rows continue to fold into native TOP clauses even when the UI native query option is grayed out.

-- Captured Azure Data Studio Profiler Trace Event
-- Proves Table.FirstN folded into a TOP clause despite UI indicators
SELECT TOP (100) 
    [CustomerID],
    [City],
    ABS([AccountBalance]) AS [AccountBalance]
FROM [dbo].[Accounts];

Key Takeaway: Profiler traces are the absolute source of truth for validating relational query pushdown.

4. Query Diagnostics for Non-Relational Sources

The built-in Query Diagnostics tool validates query folding without requiring external database profilers. It supports both relational databases and non-relational sources like OData feeds.

• Mechanism: Select Diagnose Step under the Tools tab in the Power Query ribbon to record performance and query evaluation metrics for a specific step.
• Under the Hood: Power Query generates detailed diagnostic evaluation tables that capture outbound network requests, query execution details, and evaluation times.
• Best Practice: Filter the diagnostic table's Operation column to get response stream to inspect outgoing URLs and verify that filters were pushed as server-side parameters.

// Outbound OData URI captured in Query Diagnostics 'get response stream'
// Confirms $select and $filter folded to the OData service
https://services.odata.org/V4/Northwind/Northwind.svc/Orders?$select=OrderID,CustomerID&$filter=ShipCountry eq 'France'

Key Takeaway: Use Query Diagnostics to verify server-side query parameter pushdown on non-relational endpoints.

Topics Covered in Diagnosing & Fixing Broken Query Folding in Power Query

1. Query Folding Fundamentals (0:00 - 1:26) — The instructor explains query folding principles and contrasts supported native calculations against unsupported text transformations.
2. Applied Steps Native Query (1:26 - 3:22) — The instructor demonstrates how to inspect native SQL queries through the Applied Steps context menu and observes grayed-out states.
3. Azure Data Studio Tracing (3:22 - 5:03) — An external profiler trace is configured in Azure Data Studio to capture SQL Server events and confirm hidden folding.
4. Query Diagnostics on OData (5:03 - 7:44) — The instructor uses the Diagnose Step tool to verify server-side query parameter folding on an OData web feed.

Reference Cheat Sheet

• View Native Query — Shows generated native database query in Applied Steps

• Number.Abs — Calculates absolute values and translates to native SQL ABS

  Table.TransformColumns(Source, {{"Amount", Number.Abs, type number}})
  

• Text.Proper — Capitalizes each word but typically breaks SQL query folding

  Table.TransformColumns(Source, {{"City", Text.Proper, type text}})
  

• Table.FirstN — Returns top rows, translating to SQL TOP clauses

  Table.FirstN(#"PreviousStep", 100)
  

• Diagnose Step — Runs Power Query diagnostics on the selected step

• get response stream — Diagnostic operation filter value displaying outbound HTTP/OData requests

  Table.SelectRows(Diagnostics, each ([Operation] = "get response stream"))
  

Comparison Table

Common Pitfalls

• Mistake: Assuming query folding broke because View Native Query is grayed out. Avoid: Confirm actual folding execution using Azure Data Studio profiler traces or Query Diagnostics.
• Mistake: Applying non-folding text transformations before essential row filters. Avoid: Position native-supported filters and row limits before transformations that break folding.
• Mistake: Relying on SQL profiler tools to verify folding on OData endpoints. Avoid: Use the built-in Diagnose Step tool to inspect pushed URL query parameters.

FAQs

• Why is 'View Native Query' disabled after adding a 'Keep Top Rows' step? Power Query's UI cannot always generate the display script for partial wrapper operations, even though the step folds into a SQL TOP clause at runtime.
• How can I confirm that an OData connector is folding query filters? Run Diagnose Step and filter the diagnostic evaluation table to 'get response stream' to inspect whether $filter parameters were pushed in the request URL.
• What happens when query folding completely fails in a data pipeline? Power Query downloads the entire unfiltered dataset from the source and performs all subsequent filtering and transformations using local computer memory.