Power Query SQL Pushdown and Query Folding for Maximum Performance in Excel and Power BI

Contents

1. What SQL pushdown means in Power Query
   1. Core benefits of SQL pushdown and query folding
   2. Where SQL pushdown is available
2. How query folding converts M code into SQL
   1. Example of M code and folded SQL
   2. Partial folding and the last foldable step
3. Detecting SQL pushdown with View Native Query
   1. Using View Native Query in Power BI Desktop
   2. Using View Native Query in Excel’s Power Query
4. Transformations that typically support SQL pushdown
   1. Transformations that often break folding
5. Patterns for designing queries that maximize SQL pushdown
   1. Filter early on the server
   2. Select only required columns
   3. Use standard aggregations where possible
   4. Avoid premature buffering and custom row logic
6. SQL pushdown with Value.NativeQuery
   1. Example of Value.NativeQuery
   2. Trade-offs of writing native SQL
7. SQL pushdown in Import vs DirectQuery models
   1. Import mode
   2. DirectQuery mode
8. Common anti-patterns that block SQL pushdown
   1. Refactoring strategy
9. Practical checklist for Power Query SQL pushdown
10. FAQ

This article explains how SQL pushdown and query folding work in Power Query, and shows advanced techniques to optimize data models, refresh performance, and server-side processing for both Excel and Power BI.

1. What SQL pushdown means in Power Query

SQL pushdown in Power Query is the process of translating transformation steps in M code into native SQL that runs directly on the source database.

Instead of pulling all rows into the client and transforming them locally, Power Query tries to fold as many steps as possible into a single native query that runs on the database engine.

This behavior is Power Query’s implementation of query folding.

1.1 Core benefits of SQL pushdown and query folding

• Reduced data movement. Only necessary rows and columns are transferred to the client.
• Leverages database engine. Filters, joins, aggregations, and calculations run on a highly optimized SQL engine.
• Faster refresh cycles. Smaller result sets and server-side processing shorten refresh times.
• Scalability. SQL pushdown allows Power Query solutions to scale to large fact tables and complex joins.

1.2 Where SQL pushdown is available

SQL pushdown is available when the connector supports query folding and a native SQL dialect, for example.

• SQL Server and Azure SQL Database.
• Azure Synapse Analytics (SQL pools).
• Many relational databases such as Oracle, PostgreSQL, MySQL, and others.
• Some OData feeds and other structured sources with foldable providers.

For flat files such as CSV, Excel workbooks, or JSON, there is no SQL engine behind the source, so query folding and SQL pushdown are not available.

2. How query folding converts M code into SQL

Each step in a Power Query query is an expression in M language, usually in the form of a sequence of table transformations.

When possible, the query engine constructs a native SQL statement that represents the entire sequence from the initial source step up to the last foldable step.

2.1 Example of M code and folded SQL

Consider a basic query against a SQL Server table.

let Source = Sql.Database("SQLSERVER01", "SalesDW"), dbo_FactSales = Source{[Schema="dbo", Item="FactSales"]}[Data], FilteredRows = Table.SelectRows(dbo_FactSales, each [OrderDate] >= #date(2024, 1, 1)), SelectedColumns = Table.SelectColumns(FilteredRows, {"OrderDate", "CustomerKey", "SalesAmount"}), GroupedRows = Table.Group( SelectedColumns, {"CustomerKey"}, {{"TotalSales", each List.Sum([SalesAmount]), type number}} ) in GroupedRows

If all connectors and data types support folding, Power Query can generate a single SQL query similar to the following.

SELECT [CustomerKey], SUM([SalesAmount]) AS [TotalSales] FROM [dbo].[FactSales] WHERE [OrderDate] >= '2024-01-01' GROUP BY [CustomerKey];

The filter, column projection, and aggregation have all been pushed down into the database, which is exactly SQL pushdown.

2.2 Partial folding and the last foldable step

Query folding is not all-or-nothing.

Power Query keeps folding steps into SQL until it encounters a transformation that the provider cannot represent in native SQL.

From that step onward, remaining transformations are performed in the Mashup Engine on the client.

This creates a concept of the last foldable step.

Note : Any transformation added after the last foldable step will run locally and can significantly increase refresh times for large datasets.

3. Detecting SQL pushdown with View Native Query

To confirm whether SQL pushdown is occurring, you should use the View Native Query feature in Power Query.

3.1 Using View Native Query in Power BI Desktop

1. Open Power Query Editor.
2. Select the query in the Queries pane.
3. Select a step in the Applied Steps list.
4. On the Home or View tab, select “View Native Query”.

If the option is enabled and shows a SQL statement, that step is still part of the folded portion of the query.

If the menu item is disabled (greyed out), folding has been broken at that step or earlier.

3.2 Using View Native Query in Excel’s Power Query

The behavior in Excel is similar.

1. Data tab → Get Data → Launch Query Editor.
2. Select the query and choose a foldable step.
3. Right-click the step or query and select “View Native Query”.

An available SQL preview indicates that SQL pushdown is happening for all steps up to that point.

4. Transformations that typically support SQL pushdown

The following common transformations usually support query folding and can be pushed down into SQL when using a foldable connector.

• Row filters (Table.SelectRows with simple predicates).
• Column selection (Table.SelectColumns).
• Sorting (Table.Sort).
• Grouping and aggregations (Table.Group with standard aggregates like Sum, Count, Min, Max, Average).
• Joins between tables on related columns.
• Simple arithmetic and scalar expressions on numeric columns.

4.1 Transformations that often break folding

The following patterns often stop SQL pushdown.

• Using Table.Buffer unnecessarily.
• Complex custom functions called per row (Table.AddColumn with arbitrary M code).
• Operations that require in-memory list processing or cross-row logic that cannot map to SQL.
• Referencing other queries that have already broken folding.
• Adding index columns in some providers where they cannot be expressed in SQL.

Transformation	Typical folding behavior	Notes for SQL pushdown
Row filter on equality or range	Foldable	Translates to WHERE clause.
Column selection	Foldable	Translates to column list in SELECT.
Join between two SQL tables	Foldable	Translates to JOIN in SQL.
Group and Sum	Foldable	Translates to GROUP BY with aggregates.
Custom column using complex M logic	Often not foldable	May force evaluation in the Mashup Engine.
Table.Buffer	Breaks folding	Forces materialization in memory.

5. Patterns for designing queries that maximize SQL pushdown

To take full advantage of SQL pushdown with Power Query, design your queries so that costly operations are pushed into SQL and non-foldable operations are minimized or moved to later layers.

5.1 Filter early on the server

Apply filters as early as possible in the query, ideally right after the source step.

let Source = Sql.Database("SQLSERVER01", "SalesDW"), dbo_FactSales = Source{[Schema="dbo", Item="FactSales"]}[Data], FilteredRows = Table.SelectRows( dbo_FactSales, each [OrderDate] >= #date(2024, 1, 1) and [OrderDate] < #date(2025, 1, 1) ) in FilteredRows

These filters fold to the SQL WHERE clause and drastically reduce data volume.

5.2 Select only required columns

Project only the columns you need for your data model.

SelectedColumns = Table.SelectColumns( FilteredRows, {"OrderDate", "CustomerKey", "ProductKey", "SalesAmount"} )

This step folds into the SELECT list and reduces network bandwidth and memory consumption.

5.3 Use standard aggregations where possible

When you aggregate in Power Query, prefer standard aggregates that SQL can represent.

• Sum, Count, Min, Max, Average, and standard deviations are good candidates.
• Avoid custom aggregation functions that rely on M-only logic.

5.4 Avoid premature buffering and custom row logic

Only use Table.Buffer when you have a specific and validated need to protect a query from multiple evaluations, and only after folding is complete.

Likewise, avoid per-row custom M functions until after all critical folding-friendly steps have been defined.

Note : If you must use complex M logic, consider splitting the query into two layers: a foldable “staging” query that performs all SQL pushdown steps, and a thin “presentation” query that references the staging query and adds final non-foldable logic.

6. SQL pushdown with Value.NativeQuery

In some scenarios, you want full control over the SQL that is sent to the database.

Power Query allows this through the Value.NativeQuery function.

6.1 Example of Value.NativeQuery

let Source = Sql.Database("SQLSERVER01", "SalesDW"), Query = " SELECT CustomerKey, SUM(SalesAmount) AS TotalSales FROM dbo.FactSales WHERE OrderDate >= @StartDate AND OrderDate < @EndDate GROUP BY CustomerKey ", Parameters = [ StartDate = #date(2024, 1, 1), EndDate = #date(2025, 1, 1) ], Native = Value.NativeQuery(Source, Query, Parameters) in Native

This approach gives you explicit SQL pushdown because you provide the exact query that runs on the server.

6.2 Trade-offs of writing native SQL

• Advantages. Full control over joins, hints, indexing strategies, and advanced features like window functions.
• Disadvantages. Less flexibility in visual editing, more dependency on database skills, and additional maintenance when schema changes.

A common pattern is to use native SQL for complex logic that Power Query cannot fold, while still using the graphical interface for simpler transformations.

7. SQL pushdown in Import vs DirectQuery models

Power BI supports both Import and DirectQuery storage modes, and SQL pushdown plays an important role in both.

7.1 Import mode

• During refresh, Power Query pushes as many transformations as possible to SQL.
• The final result is loaded into the in-memory model.
• Good folding reduces refresh time and resource usage.

7.2 DirectQuery mode

• Queries run against the source at report runtime.
• Model relationships and filters are translated into SQL queries against the underlying tables.
• Making source views and Power Query queries fold efficiently is critical for interactive performance.

Note : In DirectQuery, poor SQL pushdown and non-foldable transformations can cause slow visuals, timeouts, and heavy load on the database because every interaction triggers new queries.

8. Common anti-patterns that block SQL pushdown

The following anti-patterns frequently prevent Power Query from performing effective SQL pushdown.

• Mixing data from foldable sources (SQL) and non-foldable sources (Excel, CSV) in the same query chain.
• Applying complex transformations on top of previously non-foldable steps.
• Using type conversions or custom functions that the provider does not recognize.
• Placing Table.Buffer too early and forcing local evaluation prematurely.

8.1 Refactoring strategy

A typical refactoring strategy to restore SQL pushdown is.

1. Create one or more staging queries that connect to SQL sources and only use foldable transformations.
2. Load these staging queries as connections only or as intermediate tables.
3. Create final presentation queries that reference the staging queries and apply any remaining non-foldable logic.

This pattern isolates non-foldable logic away from server-intensive operations and preserves efficient SQL execution.

9. Practical checklist for Power Query SQL pushdown

When designing or reviewing Power Query solutions, use the following checklist.

• Is the primary source a foldable SQL-based connector.
• Are filters applied as early as possible directly to the source.
• Are only needed columns selected at the staging layer.
• Do joins and aggregations use patterns that translate cleanly into SQL.
• Is View Native Query enabled for key steps.
• Are non-foldable steps isolated into thin presentation queries.
• Is Table.Buffer used only when necessary and after folding is complete.
• Has the impact of DirectQuery vs Import mode on SQL pushdown been evaluated.

FAQ

How can I quickly check if SQL pushdown is working in my Power Query?

The fastest method is to use View Native Query on a step that you expect to be foldable.

If the option is enabled and shows a SQL statement, SQL pushdown is in effect for that part of the query.

If it is greyed out, folding has broken earlier and later steps will run in the Mashup Engine.

Does every Power Query connector support query folding and SQL pushdown?

No.

Only connectors that have a foldable provider and a native query language, such as SQL Server or other relational databases, support SQL pushdown.

File-based sources and many web APIs do not support folding and therefore cannot benefit from SQL pushdown.

Will using Table.Buffer always break query folding?

In most scenarios, applying Table.Buffer to a foldable query will stop further folding because it forces the data to be materialized in memory.

There are edge cases where buffering occurs after folding is complete, but as a general rule, you should treat Table.Buffer as a folding boundary and use it sparingly.

Is it better to write native SQL or rely on the graphical Power Query interface?

It depends on your requirements and skill set.

The graphical interface is more maintainable and easier for non-SQL experts, but it might not express very complex logic.

Native SQL can provide precise control and advanced features, but it requires more effort, proper version control, and coordination with database administrators.

How does DirectQuery change the importance of SQL pushdown?

In DirectQuery, SQL pushdown becomes critical because every interaction in the report triggers live queries against the database.

Efficient folding and well-designed SQL pushdown are essential to prevent slow performance, timeouts, and excessive load on the data source.

How to Fix GC Peak Fronting: Causes, Diagnostics, and Proven Solutions →

Nitrogen Purge Efficiency: Proven Methods to Cut Gas Use and Purge Time →

GC Peak Tailing Troubleshooting: Proven Fixes for Sharp, Symmetric Peaks →

추천·관련글

• Industrial Waste Phase Separation Troubleshooting: How to Break Stable Emulsions and Restore Settling
• Fix NMR Shimming Failure: Expert Troubleshooting Guide for Sharp, Stable Spectra
• Prevent UV-Vis Absorbance Saturation: Expert Strategies for Accurate Spectrophotometry
• Correct Curved ICP-OES Calibration: Expert Methods to Restore Linearity
• Handle Moisture Contamination of Reagents: Proven Drying Methods, Testing, and Storage Best Practices
• Lithium Dendrite Safety: Diagnosis, Mitigation, and Emergency Response