Query Folding and Load Performance

Quick Answer: Query folding translates Power Query steps into native data source queries (like SQL), so the source does the heavy lifting instead of Power BI. Check folding status by right-clicking a step — if "View Native Query" is available, folding is active. Steps like Filter Rows and Remove Columns fold well; Custom Columns and complex M functions break folding.

What is Query Folding?

Query folding is the mechanism by which Power Query translates transformation steps into native queries that are executed by the data source. Instead of downloading all data and transforming it locally, the data source performs the work.

Without Query Folding

Data Source → Download ALL rows → Power BI filters locally → Load result

With Query Folding

Data Source (executes WHERE, GROUP BY, etc.) → Returns only needed data → Load result

The performance difference can be dramatic — especially with large databases.

Checking Folding Status

Right-click any step in the Applied Steps pane:

Steps That Support Folding

These operations are typically translated to native SQL:

Steps That Break Folding

Once folding breaks at a step, ALL subsequent steps are also non-foldable:

Optimizing for Query Folding

Rule 1: Foldable Steps First

Put foldable transformations (filter, remove columns, type changes) before non-foldable ones:

Bad order (folding breaks early):

1. Add Custom Column (breaks folding)
2. Filter Rows (not folded — runs locally on all data)
3. Remove Columns (not folded)

Good order (maximizes folding):

1. Filter Rows (folded — source returns less data)
2. Remove Columns (folded — source returns fewer columns)
3. Add Custom Column (runs locally, but on smaller dataset)

Rule 2: Filter Early

Reduce the data volume as early as possible:

// Apply filters that fold to the source
Table.SelectRows(Source, each [Year] >= 2024 and [Status] <> "Cancelled")

Rule 3: Select Only Needed Columns

Remove unnecessary columns early to reduce data transfer:

// Remove columns you don't need
Table.RemoveColumns(Source, {"InternalNotes", "TempFlag", "Debug"})

Rule 4: Use Native Query When Needed

For maximum control, write a native SQL query directly:

let
    Source = Sql.Database("server", "database",
        [Query="SELECT OrderID, CustomerID, Amount FROM Sales WHERE Year >= 2024"])
in
    Source

Warning: Native queries disable query folding for subsequent steps because Power BI treats the result as a flat table.

Additional Load Performance Tips

Incremental Refresh

For large datasets, configure incremental refresh to only refresh new or changed data:

1. Create RangeStart and RangeEnd parameters (Date/Time type)
2. Filter your data query using these parameters
3. Configure the incremental refresh policy in Power BI Desktop
4. Publish to the Service where it automatically manages partitions

Aggregation Tables

Pre-aggregate data in Power Query for summary-level queries:

1. Create a reference query from the detail table
2. Apply Group By to aggregate to the needed level
3. Load both detail and aggregate tables
4. Configure aggregations in the model to route queries automatically

Disable Auto Date/Time

Power BI creates hidden date tables for each date column by default, increasing model size:

File → Options → Data Load → Uncheck "Auto Date/Time for new files"

Disable Background Data

Turn off background data loading during development:

File → Options → Data Load → Uncheck "Allow data preview to download in the background"

On the Exam

The PL-300 frequently tests:

• Understanding what query folding is and why it matters
• Identifying which steps support or break folding
• Optimizing step order to maximize folding
• Knowing how to check folding status (View Native Query)
• Configuring incremental refresh parameters