Adiabatic Mixing Temperature Calculator: Temperature-Dependent Cp(T) Energy Balance Guide

on

Mastering Query Folding Diagnostics in Excel Power Query for Faster Refresh Performance

on

This article explains how to diagnose and optimize query folding in Excel Power Query by using folding indicators, native query inspection, and Power Query Diagnostics techniques so that you can systematically improve data refresh performance in real-world Excel models.

1. Understanding query folding in Excel Power Query

Query folding is the process where Power Query translates your transformation steps into a single optimized query that runs on the data source instead of in Excel.

When you connect to a structured data source such as SQL Server, OData, or certain cloud data warehouses, Power Query tries to push filters, joins, grouping, and other operations back to the source engine. The source then returns only the already-filtered and aggregated results, which are usually much smaller and faster to process than raw data.

Conceptually, the engine evaluates your M script, determines which steps can be expressed in the source’s native language (for example SQL), and offloads those steps. This optimization process is called query folding and can result in:

  • Reduced data volume transferred into Excel.
  • Less load on the Power Query engine inside Excel.
  • Shorter refresh times and more scalable models.

Depending on how your query is written and the connector capabilities, you will see three typical outcomes:

  • Full folding: All transformation steps are executed by the data source.
  • Partial folding: Some steps are executed by the source, the rest by the Power Query engine.
  • No folding: All transformations are executed locally in Power Query after retrieving raw data.

Because Excel uses the same Power Query engine as Power BI and other Microsoft products, the folding behavior is conceptually identical, even though some diagnostics user interface elements are only exposed in Power BI Desktop.

2. Diagnostics in Excel vs Power BI: what you actually have

Power Query runs in multiple hosts: Excel, Power BI Desktop, SSAS, Dataflows, and others. The engine and M language are shared, but not all hosts expose the same diagnostics features in their user interface.

As of recent versions, the Tools > Query Diagnostics ribbon (Start Diagnostics, Stop Diagnostics, Diagnose Step) is officially documented and available for Power Query in Power BI Desktop.

In Excel, you do not see the same Tools ribbon with Query Diagnostics buttons, but you still can diagnose query folding using a combination of features:

  • Query folding indicators in the Applied Steps pane.
  • Right-click View Native Query on folding-capable steps.
  • Optional query plan and Value.NativeQuery patterns for advanced scenarios.
  • Engine tracing via Options > Diagnostics, plus log analysis tools.
Capability Excel Power Query Power BI Desktop Power Query
Query folding indicators (Applied Steps) Available Available
View Native Query Available for supported connectors Available for supported connectors
Query Diagnostics ribbon (Start/Stop, Diagnose Step) Not exposed in Excel UI Available on Tools tab
Engine trace logging Available via Options > Diagnostics Available via Options > Diagnostics
Using Power Query Diagnostics to inspect folding Indirect (author in Power BI, copy M back) Direct (within Power BI Desktop)
Note : For many Excel-only scenarios, folding indicators and View Native Query are sufficient to diagnose most performance problems without using full Query Diagnostics traces.

3. Core tools to inspect query folding in Excel Power Query

3.1 Query folding indicators in the Applied Steps pane

Recent versions of Power Query show a small icon next to each step in the Applied Steps pane that indicates folding status. The exact visual design can change over time, but logically you will see:

  • A folded indicator for steps that are fully pushed to the source.
  • A not folded indicator for steps executed locally.
  • Sometimes, an intermediate indicator for partially folded steps, depending on connector and host.

Typical workflow for using folding indicators in Excel:

  1. Open Data > Get Data > Launch Power Query Editor (or right-click a query and select Edit).
  2. Click each step in Applied Steps from top to bottom.
  3. Observe the folding indicator icon for each step.
  4. Identify the last step that still shows “folded”. Any transformation after that step is executed by the engine inside Excel.

If a seemingly innocuous transformation moves the indicator from folded to not folded, that step is a potential folding breakpoint and a candidate for refactoring.

3.2 View Native Query: inspecting the exact SQL

View Native Query is the primary diagnostic for understanding what Power Query is actually sending to the source when folding is active.

To use it in Excel Power Query with a relational source such as SQL Server:

  1. Ensure your query connects to a folding-capable source (e.g., SQL Server, some OData feeds).
  2. In the Power Query Editor, select a step that still shows a folded indicator.
  3. Right-click the step and choose View Native Query.
  4. Review the SQL (or other native language) that Power Query generated.

If the menu option is enabled and shows a query, folding is active at that step. If View Native Query is greyed out, either:

  • The connector does not support folding, or
  • A previous step already broke folding, so later steps cannot expose a native query.
Note : When View Native Query is disabled for a specific step, check earlier steps. A transformation such as adding an index column, calling an incompatible custom function, or performing certain text operations can break folding for all subsequent steps.

3.3 Query plans and Value.NativeQuery for deeper analysis

For supported connectors, Power Query can expose a query plan view that shows how folding and native queries interact. This is especially relevant when you use Value.NativeQuery with the optional [EnableFolding = true] parameter.

A typical pattern for advanced users:

  1. Connect to the database (for example SQL Server) at the appropriate scope (server or database).
  2. In the formula bar, wrap the Source step with Value.NativeQuery and pass your custom SQL plus the options record.
let Source = Sql.Database("MyServer","AdventureWorks2019"), Native = Value.NativeQuery( Source, "SELECT DepartmentID, Name FROM HumanResources.Department", null, [EnableFolding = true] ) in Native

Once this is in place, you can still apply additional transformations (filters, groupings, joins) after Native. If folding remains active, those downstream steps will be pushed as additional predicates or projections around the native statement.

Although the query plan UI is more prominent in Power BI Desktop today, understanding this pattern helps Excel users reason about how their queries might be folded and where diagnostics in another host can guide refactoring.

4. Using Power Query Diagnostics from Power BI to tune Excel queries

Because the underlying Power Query engine is the same, you can use Power BI Desktop as a diagnostics “laboratory” even when your final model must live in Excel.

The documented Query Diagnostics feature is primarily exposed in Power BI Desktop: you can start tracing, refresh a query, and get detailed Aggregated and Detailed diagnostics tables.

A practical workflow:

  1. Copy the M script for your slow Excel query (Home > Advanced Editor, then copy everything inside let … in).
  2. Paste this script into a new blank query in Power BI Desktop.
  3. On the Tools tab in the Power Query Editor, choose Start Diagnostics.
  4. Refresh the query (or Diagnose Step for a specific step).
  5. Stop diagnostics and review the generated diagnostics queries (Aggregated and Detailed).
  6. Use the diagnostics data to identify:
    • Which data source queries are emitted.
    • Where the most time is spent (network, server, engine).
    • Whether repeated evaluations or privacy partitions are causing overhead.
  7. Refactor the M script based on these findings and copy it back into Excel.
Note : This approach is viable because M is portable across hosts as long as you use the same connectors and privacy settings. Always test the optimized script back in Excel to confirm that refresh behavior and credentials work as expected.

5. Step-by-step example: diagnosing a slow Excel Power Query

5.1 Scenario description

Assume you have an Excel workbook that pulls several hundred thousand rows from SQL Server, applies multiple filters and joins, and then loads into the Data Model. Refresh now takes several minutes, which is too slow for daily use.

5.2 Initial inspection

  1. Open the query in Excel Power Query Editor.
  2. Walk through the Applied Steps and inspect the folding indicator for each step.
  3. Right-click the last step that still shows as folded and choose View Native Query to see the generated SQL.

In many cases you will observe a pattern like:

  • Source (folded).
  • Filtered Rows (folded).
  • Removed Columns (folded).
  • Added Custom (not folded).

Here, the Added Custom step is your folding breakpoint.

5.3 Refactoring the M script

Suppose your M script for the problematic step looks like this:

let Source = Sql.Database("MyServer", "SalesDW"), dbo_FactSales = Source{[Schema="dbo",Item="FactSales"]}[Data], #"Filtered Rows" = Table.SelectRows( dbo_FactSales, each [OrderDate] >= #date(2023,1,1) ), #"Added Custom" = Table.AddColumn( #"Filtered Rows", "YearMonth", each Date.Year([OrderDate])*100 + Date.Month([OrderDate]), Int64.Type ) in #"Added Custom"

Depending on the connector, the custom column may or may not fold. If it does not fold (indicator shows not folded and View Native Query is disabled from that step onward), consider:

  • Implementing the calculation as a native SQL expression by editing the underlying view or source query.
  • Using Value.NativeQuery to precompute YearMonth in SQL and then let further steps remain foldable.

One adjusted pattern using Value.NativeQuery could be:

let Source = Sql.Database("MyServer", "SalesDW"), Native = Value.NativeQuery( Source, " SELECT OrderDate, CustomerKey, ProductKey, Quantity, Amount, YEAR(OrderDate)*100 + MONTH(OrderDate) AS YearMonth FROM dbo.FactSales WHERE OrderDate >= @StartDate ", [StartDate = #date(2023,1,1)], [EnableFolding = true] ), #"Changed Type" = Table.TransformColumnTypes( Native, {{"YearMonth", Int64.Type}} ) in #"Changed Type"

If the folding indicator remains active after Changed Type, your YearMonth logic is now executed on SQL Server, reducing computation in Excel and often improving performance.

5.4 Validating improvements

After refactoring:

  1. Refresh the query in Excel and measure the new refresh time.
  2. Confirm that:
    • The folding indicator is still active for critical steps.
    • View Native Query shows the expected SQL with filters and projections.
    • Result row counts and calculations match the original logic.

6. Common patterns that break query folding

To use query folding diagnostics effectively, you need to recognize transformations that typically break folding so you can either avoid them or move them later in the pipeline.

Pattern Impact on folding Recommended mitigation
Custom column with complex M logic (loops, recursion, non-foldable functions) Often breaks folding at and after this step Push logic to SQL view, stored procedure, or Value.NativeQuery; or move to a later step after all heavy filtering is done
Using certain text transformations (e.g., specific capitalization or locale-sensitive functions) May be unsupported by source, resulting in local execution Use SQL-native string functions where possible or restrict these transformations to reduced datasets
Joining a foldable table to a non-foldable source (e.g., CSV, Excel file) For many connectors, folding stops once a non-foldable source is combined Perform joins inside the database when possible, or stage data into a foldable store
Invoking custom functions row by row Typically forces row-by-row evaluation in the engine Refactor to set-based operations or implement logic in the source system
Changing data source privacy levels in a way that forces isolation Can cause multiple evaluations and prevent certain optimizations Align privacy levels and, where appropriate, set compatible levels across sources
Note : Not every non-folding operation is a problem. Diagnostics should focus on heavy data volumes and expensive transforms. Small post-processing steps after folding completes may be acceptable if they simplify the model.

7. Best-practice checklist for query folding diagnostics in Excel

The following checklist summarizes a high-level process for systematically diagnosing and tuning query folding in Excel Power Query:

  • Start with the data source. Whenever possible, use structured data sources that support folding (SQL Server, OData, supported cloud warehouses) instead of flat files.
  • Filter and project early. Apply column removal and basic filters as early as possible while folding is still active.
  • Use folding indicators. Treat the last folded step as your performance boundary and keep heavy operations above that boundary.
  • Leverage View Native Query. Regularly inspect the generated SQL or native query to verify that transformations are pushed down and to spot redundant expressions.
  • Offload complex logic. Move complex calculations and joins to the database where indexing and query optimizers can help.
  • Stage queries. Build reusable, foldable staging queries (for example, “FactSales_Staging”) and reference them from downstream queries to avoid duplicated transformations.
  • Use Power BI Desktop for deep diagnostics. For particularly slow queries, temporarily analyze them with Query Diagnostics in Power BI Desktop, then port the optimized M back into Excel.
  • Monitor refresh over time. Re-run diagnostics after schema changes, new columns, or additional filters are introduced.

FAQ

Can I use the Query Diagnostics ribbon directly in Excel Power Query?

As of current releases, the documented Query Diagnostics ribbon (Start Diagnostics, Stop Diagnostics, Diagnose Step) is exposed in the Power Query editor of Power BI Desktop rather than in Excel. In Excel, you still benefit from the same engine but rely on folding indicators, View Native Query, and trace logging instead. If you need full diagnostics tables, copy the M script into Power BI Desktop, run Query Diagnostics there, and copy the optimized script back into Excel.

Why is “View Native Query” always greyed out in my Excel queries?

If View Native Query is disabled for all steps, common reasons include:

  • The connector does not support query folding (for example, many text, CSV, or Excel file sources).
  • You are using a transformation that breaks folding very early in the query, such as a complex custom column or a non-foldable function.
  • The query starts from a native SQL statement that is not configured for folding in the current pattern.

Test by creating a minimal query against a folding-capable source (such as a simple SQL Server table) with no extra transformations. If View Native Query is enabled there, your original query likely contains a folding-breaking step that needs refactoring.

Does query folding matter if my source is a CSV or Excel file?

For flat file sources, there is typically no folding because the source engine cannot execute queries in its own language. All transformations are executed in the Power Query engine. In that case, diagnostics focus on reducing data size early (filtering columns and rows), avoiding expensive row-by-row custom functions, and using efficient data types. Folding diagnostics become critical once you move to relational or OData sources that can push work server-side.

How can I tell if a specific transformation will fold before I commit to it?

There is no universal visual predictor, but you can follow a quick loop:

  1. Apply the transformation.
  2. Check the folding indicator on the new step.
  3. Right-click and see if View Native Query is available.

If folding breaks, undo the step, consider an alternative (for example, a SQL expression), or move the step later in the pipeline so that upstream heavy filtering still folds.

Is it worth learning M to improve query folding diagnostics?

Yes. While you can build many solutions via the graphical UI alone, understanding the underlying M script helps you recognize patterns that impact folding and performance. It also makes it easier to copy queries between Excel and Power BI, and to apply advanced patterns such as Value.NativeQuery with EnableFolding. Many professional references on Power Query in Excel and Power BI combine folding concepts with M-level explanations.

:
추천·관련글