Introduction
Query folding is one of the most important, yet least understood, concepts in Power BI. Many Power BI performance problems, especially with large datasets, happen because query folding is broken without users realizing it. When query folding works correctly, Power BI pushes data processing back to the data source. When it does not, Power BI pulls more data than necessary and processes it locally, which slows down reports and refreshes.
This article explains query folding in simple terms, how it works, why it matters for performance, and how it affects large Power BI datasets in real-world scenarios.
1. What Query Folding Means
Query folding means that Power BI translates the steps you apply in Power Query into a query that runs directly on the data source.
Instead of Power BI loading all the data and then filtering or transforming it, the data source handles the heavy lifting first.
For example, if you filter data by date in Power Query and query folding works, only the filtered rows are sent to Power BI.
2. Why Query Folding Is Critical for Performance
Data sources such as SQL databases are designed to efficiently handle large volumes of data. Power BI is not meant to process millions of rows unnecessarily.
When query folding is enabled, Power BI refreshes faster and uses less memory.
For example, filtering one year of data in the database is much faster than importing ten years of data and filtering later.
3. What Happens When Query Folding Breaks
When query folding breaks, Power BI downloads more data than required and applies transformations locally.
This increases refresh time, memory usage, and can even cause refresh failures with large datasets.
For example, adding a step that Power BI cannot translate to SQL may force the entire table to be loaded first.
4. Common Actions That Break Query Folding
Certain Power Query actions often prevent query folding. These include using custom columns with complex logic, applying row-by-row transformations, or using unsupported functions.
Once folding breaks, all steps after that point run locally.
For example, adding an index column early in the query can stop folding and slow down refresh performance.
5. Query Folding and Large Datasets
With large datasets, query folding becomes even more important. Without it, Power BI may attempt to process millions of rows locally.
This can lead to long refresh times or out-of-memory errors.
For example, enterprise datasets with hundreds of millions of records rely heavily on query folding to remain scalable.
6. How to Check If Query Folding Is Working
Power BI provides a simple way to check query folding. Users can right-click a step in Power Query and see if the option to view the native query is available.
If the option is disabled, query folding has stopped at that step.
For example, checking this early helps identify which transformation caused the issue.
7. Query Folding in Import vs DirectQuery
Query folding works differently depending on the storage mode. In Import mode, folding affects refresh performance. In DirectQuery mode, it affects every user interaction.
Breaking query folding in DirectQuery has a much bigger impact because queries run repeatedly.
For example, a broken fold in DirectQuery can make every filter change slow.
8. Query Folding and Incremental Refresh
Incremental Refresh depends heavily on query folding. Power BI must be able to push date filters to the data source.
If folding is broken, incremental refresh will not work correctly.
For example, using unsupported transformations on date columns can disable incremental refresh entirely.
9. Best Practices to Preserve Query Folding
To preserve query folding, apply filters as early as possible, avoid unnecessary custom logic, and use supported connectors and functions.
Always test folding behavior after adding new steps.
For example, moving complex calculations to the data source instead of Power Query often improves performance.
10. When Breaking Query Folding Is Acceptable
In some cases, breaking query folding is unavoidable. Small datasets or final formatting steps may not cause noticeable issues.
The key is knowing when it is safe and when it is dangerous.
For example, minor text formatting on a small dimension table usually has minimal impact.
Summary
Query folding plays a critical role in Power BI performance, especially with large datasets. When folding works, data sources handle heavy processing efficiently. When it breaks, Power BI must do extra work, leading to slow refreshes and poor report performance. By understanding how query folding works, checking it regularly, and designing transformations carefully, teams can build scalable and high-performing Power BI solutions.