Query Folding

Power BI · Advanced Features

Theory

Query folding is Power Query's ability to translate your applied steps into a single query in the source's own language, usually SQL, so the source does the work and only the result is sent to Power BI. Steps like filtering rows, removing columns, renaming, grouping and joining typically fold against relational databases. For example, connecting to SQL Server, keeping five columns and filtering OrderDate to 2024 can fold into one SELECT ... WHERE OrderDate >= '2024-01-01' statement instead of downloading the whole table.

Folding stops at the first step the source cannot express, and every later step then runs in the Power Query engine. Flat files such as CSV and Excel never fold. You can check by right-clicking a step: if View Native Query is available, that step folds. Putting foldable steps first and complex ones last keeps as much work as possible in the source.

Why we use it

Folding makes refresh faster and lighter, because the database filters and aggregates before data travels. It is also required for incremental refresh to work efficiently and for DirectQuery to work at all.

When to use it

Care about folding whenever the source is a database and tables are large. For small files it matters little. If a needed step breaks folding, move it later, push the logic into a source view, or use a native query.

How it works

  1. Power Query evaluates the steps
  2. Can the source express the next step? Yes: Add it to the native query. No: Stop folding here.
  3. Steps after the break run in the Power Query engine
  4. Source returns only the needed rows and columns
  5. Faster refresh, less data moved

Syntax

Power Query → right-click a step → View Native Query (greyed out usually means no folding)
Fold-friendly: filter rows, remove/select columns, rename, group by, merge, sort
Often break folding: adding an index column, some custom M functions, combining different sources

Example

Folding-friendly order for SQL Server:
1. Source = Sql.Database("sql-prod-01", "SalesDB")
2. Navigation → dbo.Sales
3. Removed Other Columns (folds)
4. Filtered Rows: OrderDate >= 2024-01-01 (folds)
5. Grouped Rows by Region (folds)
6. Added Index column (breaks folding; keep it last)
Right-click step 5 → View Native Query shows one SELECT ... WHERE ... GROUP BY.

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic