Query folding in Power Query in Power BI - Deep Dive

Overview - Query folding in Power Query

What is it?

Query folding is the process where Power Query pushes data transformation steps back to the source database instead of doing them locally. This means that the heavy work of filtering, sorting, or aggregating data happens where the data lives. It helps make data refreshes faster and reduces the load on your computer.

Why it matters

Without query folding, Power Query would download all data first and then transform it, which can be slow and use a lot of resources. Query folding makes data processing efficient and scalable, especially with large datasets. It allows users to work smoothly with big data without needing powerful local machines.

Where it fits

Before learning query folding, you should understand basic Power Query transformations and data sources. After mastering query folding, you can explore advanced performance tuning, custom connectors, and optimizing dataflows in Power BI.

Mental Model

Core Idea

Query folding means letting the data source do the heavy lifting by translating your Power Query steps into source commands.

Think of it like...

It's like ordering a pizza with toppings directly from the kitchen instead of ordering a plain pizza and adding toppings yourself at home.

Power Query Steps
┌───────────────┐
│ User Actions  │
│ (Filter,     │
│  Sort, etc.) │
└──────┬────────┘
       │ Translated into
       ▼
┌───────────────┐
│ Source Query  │
│ (SQL, etc.)  │
└──────┬────────┘
       │ Executes on
       ▼
┌───────────────┐
│ Data Source   │
│ (Database)    │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What is Query Folding?

Concept: Introduces the basic idea that Power Query can send transformations to the data source.

When you use Power Query to clean or shape data, it can either do the work on your computer or ask the data source to do it. Query folding is when Power Query asks the data source to do the work by turning your steps into commands it understands.

Result

Data transformations happen at the source, reducing data transferred and speeding up refresh.

Understanding that query folding pushes work to the source helps you see why some queries run faster than others.

2

Foundation

How Power Query Steps Translate

Concept: Explains that each step in Power Query can be translated into source commands if supported.

Power Query records each transformation step you make. If the data source supports it, these steps become a single query sent to the source. For example, filtering rows in Power Query becomes a WHERE clause in SQL.

Result

Multiple steps combine into one efficient query at the source.

Knowing that steps combine into one query helps you design transformations that fold well.

3

Intermediate

Which Data Sources Support Folding?

🤔Before reading on: do you think all data sources support query folding equally? Commit to your answer.

Concept: Not all data sources can translate Power Query steps into source queries.

Sources like SQL Server, Oracle, and some cloud databases support query folding well. Others like Excel files or web data sources do not support folding because they can't run queries. Knowing your source helps predict folding behavior.

Result

You learn to expect folding only with compatible sources.

Understanding source capabilities prevents wasted effort trying to optimize folding where it’s impossible.

4

Intermediate

How to Check If Folding Happens

🤔Before reading on: do you think Power Query shows you if folding is happening? Commit to your answer.

Concept: Power Query provides tools to see if folding occurs for each step.

In Power Query Editor, right-click a step and choose 'View Native Query'. If you see a query, folding is happening. If the option is disabled, folding stopped before that step.

Result

You can identify which steps fold and which break folding.

Knowing how to check folding helps you troubleshoot and optimize your queries.

5

Intermediate

What Breaks Query Folding?

🤔Before reading on: do you think all transformations fold? Commit to your answer.

Concept: Certain transformations or custom steps stop folding from continuing.

Steps like adding custom columns with complex formulas, merging with unsupported sources, or using functions not translatable to source queries break folding. Once broken, all following steps run locally.

Result

Folding stops at the first unsupported step, reducing performance.

Understanding folding break points helps you design queries that keep folding as long as possible.

6

Advanced

Optimizing Queries for Folding

🤔Before reading on: do you think the order of steps affects folding? Commit to your answer.

Concept: The order and type of transformations affect how much folding happens.

Applying filters and aggregations early helps push more work to the source. Avoid complex local-only steps before simple filters. Also, prefer native functions over custom code to keep folding alive.

Result

Queries run faster and use less memory by maximizing folding.

Knowing how step order impacts folding lets you write efficient queries that scale.

7

Expert

Advanced Folding with Custom Connectors

🤔Before reading on: do you think custom connectors can support query folding? Commit to your answer.

Concept: Custom connectors can be designed to support query folding by translating steps into source commands.

Power Query SDK allows developers to build connectors that implement folding logic. This means even non-traditional sources can fold queries if the connector translates steps properly. This requires deep knowledge of both source query language and Power Query M language.

Result

Custom connectors enable folding for specialized data sources, improving performance.

Understanding folding at the connector level reveals how extensible and powerful Power Query can be in enterprise scenarios.

Under the Hood

Power Query builds a query plan from your transformation steps. When connected to a folding-capable source, it translates this plan into the source's native query language (like SQL). The source executes this query and returns only the needed data. If a step cannot be translated, Power Query stops folding and processes remaining steps locally.

Why designed this way?

Query folding was designed to leverage the power of database engines to handle large data efficiently. Instead of moving all data to the client, it reduces network load and speeds up processing. Alternatives like local processing were slower and less scalable, especially for big data.

User Steps in Power Query
┌───────────────┐
│ Transformations│
│ (Filter, etc.)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Query Plan    │
│ Builder       │
└──────┬────────┘
       │ Translates
       ▼
┌───────────────┐
│ Native Query  │
│ (SQL, etc.)   │
└──────┬────────┘
       │ Executes
       ▼
┌───────────────┐
│ Data Source   │
│ (Database)    │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does every Power Query step always fold to the source? Commit to yes or no.

Common Belief: All Power Query steps automatically fold to the data source.

Reality: Only steps that can be translated into the source's query language fold; others run locally.

Why it matters: Assuming all steps fold can lead to slow queries and unexpected performance issues.

Quick: Can query folding happen with Excel files as data sources? Commit to yes or no.

Common Belief: Query folding works with any data source, including Excel files.

Reality: Excel and flat files do not support query folding because they cannot run queries.

Why it matters: Expecting folding with unsupported sources wastes time optimizing steps that will always run locally.

Quick: Does the order of steps in Power Query affect query folding? Commit to yes or no.

Common Belief: The order of transformation steps does not affect query folding.

Reality: The order matters; some steps can break folding if placed before others.

Why it matters: Ignoring step order can break folding early, causing slower data refreshes.

Quick: Can custom connectors support query folding? Commit to yes or no.

Common Belief: Custom connectors cannot support query folding because they are limited.

Reality: Custom connectors can be built to support folding by translating steps into source queries.

Why it matters: Knowing this allows advanced users to extend folding benefits to new data sources.

Expert Zone

1

Some transformations partially fold, meaning only part of the step translates to the source, and the rest runs locally.

2

Query folding can be affected by privacy levels and data source permissions, which may force local processing.

3

The 'View Native Query' option only shows the query at that step, but folding may have been broken earlier.

When NOT to use

Query folding is not applicable when working with data sources that do not support query languages, such as Excel files or web APIs. In those cases, local transformations or other ETL tools should be used instead.

Production Patterns

In production, developers design queries to maximize folding by applying filters and aggregations early, avoiding complex custom columns, and monitoring folding with 'View Native Query'. They also use incremental refresh and dataflows to optimize performance.

Connections

SQL Query Optimization

Query folding builds on SQL query optimization principles by pushing transformations to the database engine.

Understanding SQL optimization helps grasp how folding translates steps into efficient source queries.

ETL (Extract, Transform, Load) Processes

Query folding is a form of pushing transformation logic upstream in ETL pipelines.

Knowing ETL concepts clarifies why folding improves data pipeline efficiency by reducing data movement.

Compiler Design

Query folding is similar to how compilers translate high-level code into optimized machine instructions.

Recognizing folding as a translation process helps understand its limits and optimization opportunities.

Common Pitfalls

#1 Assuming all transformations fold and applying complex custom columns early.

Wrong approach:

let
Source = Sql.Database("server", "db"),
Filtered = Table.SelectRows(Source, each [Sales] > 1000),
Custom = Table.AddColumn(Filtered, "NewCol", each Text.Upper([Product]))
in
Custom

Correct approach:

let
Source = Sql.Database("server", "db"),
Filtered = Table.SelectRows(Source, each [Sales] > 1000)
in
Filtered
// Add custom columns after loading if folding is critical

Root cause:

Adding custom columns early breaks folding because the function cannot be translated to SQL.

#2 Using unsupported data sources expecting folding.

Wrong approach:

let
Source = Excel.Workbook(File.Contents("data.xlsx")),
Filtered = Table.SelectRows(Source, each [Value] > 10)
in
Filtered

Correct approach:

let
Source = Excel.Workbook(File.Contents("data.xlsx")),
Filtered = Table.SelectRows(Source, each [Value] > 10)
in
Filtered
// Accept no folding or switch to database source

Root cause:

Excel files do not support query folding, so all steps run locally.

#3 Ignoring step order and placing non-foldable steps before filters.

Wrong approach:

let
Source = Sql.Database("server", "db"),
Custom = Table.AddColumn(Source, "NewCol", each [Sales] * 2),
Filtered = Table.SelectRows(Custom, each [Sales] > 1000)
in
Filtered

Correct approach:

let
Source = Sql.Database("server", "db"),
Filtered = Table.SelectRows(Source, each [Sales] > 1000),
Custom = Table.AddColumn(Filtered, "NewCol", each [Sales] * 2)
in
Custom

Root cause:

Non-foldable steps before filters stop folding early, reducing performance.

Key Takeaways

Query folding pushes data transformations to the source database, making data refresh faster and more efficient.

Not all data sources support query folding; knowing your source's capabilities is essential.

The order and type of Power Query steps affect whether folding continues or breaks.

You can check folding status using the 'View Native Query' option in Power Query Editor.

Advanced users can extend folding to new sources by building custom connectors that translate queries.