ExamFinch.com My Study

Study guide

3.4 Query Folding and Load Performance

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

3.4 Query Folding and Load Performance

Key Takeaways Explain This Topic AI

Query folding pushes Power Query transformations back to the data source as native queries (SQL), dramatically improving performance.

Folding is supported by relational databases (SQL Server, Azure SQL, Oracle) but NOT by flat files (CSV, Excel) or web sources.

Steps that break folding include adding custom columns with M functions, using complex merges, and Type changes after non-foldable steps.

The 'View Native Query' option (right-click a step) shows the generated SQL — if grayed out, folding has stopped.

For best performance, keep foldable steps at the top of the query and non-foldable steps at the bottom.

Last updated: June 2026

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:

  • Menu Option Meaning
  • View Native Query (available) Step is folded — you can see the generated SQL
  • View Native Query (grayed out) Folding has stopped at or before this step
  • Step folding indicator (icon) Some versions show a database icon for folded steps
  • Steps That Support Folding

These operations are typically translated to native SQL:

  • Operation SQL Equivalent
  • Filter Rows WHERE clause
  • Remove Columns SELECT specific columns
  • Sort Rows ORDER BY
  • Group By GROUP BY with aggregates
  • Remove Duplicates DISTINCT
  • Rename Columns Column aliases
  • Change Type CAST/CONVERT
  • Top N Rows TOP / LIMIT
  • Merge Queries (same source) JOIN
  • Steps That Break Folding

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

  • Operation Why It Breaks Folding
  • Add Custom Column (M functions) M functions have no SQL equivalent
  • Merge Queries (different sources) Cannot cross-source join in SQL
  • Add Index Column ROW_NUMBER not supported in all contexts
  • Pivot/Unpivot Complex operations without direct SQL mapping

Complex M expressions Text.Combine , List.Transform , etc.

  • Table.Buffer Forces data into memory
  • 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):

  • Add Custom Column (breaks folding)
  • Filter Rows (not folded — runs locally on all data)
  • Remove Columns (not folded)

Good order (maximizes folding):

  • Filter Rows (folded — source returns less data)
  • Remove Columns (folded — source returns fewer columns)
  • 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:

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

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

  • Create a reference query from the detail table
  • Apply Group By to aggregate to the needed level
  • Load both detail and aggregate tables
  • 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"

The Folding "Wall"

The single most-tested idea here is that folding is sequential : Power Query folds steps from the top down, and the moment one step cannot be translated to a native query, every step after it runs locally in the Power Query engine — even steps that would normally fold perfectly. This is why step order matters as much as step choice . Connect → [folds]

  • Filter → [folds] ✅ source returns less data
  • Remove Columns → [folds] ✅ fewer columns transferred
  • Add Custom Column (M) → 🧱 FOLDING WALL
  • ... everything below runs locally on the already-reduced data

Placing the folding-breaking step (custom columns, index columns, pivots) last means the source has already done the filtering and column-pruning, so the local engine works on a much smaller dataset.

Folds well Breaks folding

Filter Rows, Remove Columns, Sort, Group By, Distinct, Top N Add Custom/Index Column, Pivot/Unpivot, cross-source Merge, Table.Buffer

Exam Tip: Flat sources — CSV, Excel, JSON, the Web connector — have no query engine to push work to, so they never fold ; all transformation runs locally. Folding is a property of queryable sources like SQL Server, Azure SQL, and Synapse. To confirm folding, right-click a step and check that View Native Query is enabled.

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