When working with Power BI, efficiency is key. The way Power Query processes transformations can significantly impact data refresh times and overall performance. That’s where Query Folding comes into the picture.
Imagine you’re working with a massive sales dataset stored in a SQL database. You apply filters and transformations in Power Query to focus on specific regions or timeframes. Instead of pulling all the data into Power BI and then filtering it, Query Folding ensures those transformations are handled directly at the source. This means only the necessary data is retrieved, reducing load times and improving performance.​
But how does Query Folding work? When does it occur? And what happens when it doesn’t?
In this blog, we’ll explore everything you need to know about Query Folding from its fundamentals to advanced techniques. We’ll cover when to use it, how to check if it’s happening, and practical ways to ensure your queries remain optimized. Whether you’re a beginner or an experienced Power BI developer, mastering Query Folding will help you improve performance and streamline your data workflows.​
What is Query Folding in Power BI?
Query Folding is the process where Power Query takes the steps you apply to your data, like filters, merges, or column transformations, and pushes them back to the data source. Instead of loading the entire dataset into Power BI and then applying your transformations locally, Power BI asks the source system (like Snowflake or SQL Server) to do the work and return only the final result.
Query folding can improve query performance by minimizing the amount of data that needs to be transferred between the database and Power BI.
How it works in Power Query
Query folding is automatically applied whenever the data source and all your transformation steps are compatible. The degree of folding can be one of three types:
Full Folding: All steps are pushed to the data source.
Partial Folding: Only some transformations are pushed, with the rest processed locally.
No Folding: No steps can be pushed, and the entire process happens inside Power BI.
As you connect to your data source and begin shaping your data, Power BI tries to translate each transformation you apply into the source’s native query language, such as SQL. This means that instead of loading all the raw data into memory, Power BI sends a single, optimized query to the source system that includes all of your folded steps.
Now that you understand what query folding is and why it’s important, let’s walk through how it works in practice. The following guide shows you how to connect to a data source, apply transformations, and check for query folding every step of the way.
1. Initiating Your Query: Three Starting Points
The way you initially connect and query your data influences how query folding will behave.
The Recommended Way: Using the Default Connector
Open Power BI Desktop and select Get Data.
Choose your data source (e.g., Snowflake).
Enter your Snowflake server and warehouse details, then click OK to connect
In the Navigator window, you can browse and select the tables or views you want to use.
This is the most user-friendly method and automatically generates the M code for you. It’s designed to enable query folding by default for all subsequent supported transformations.
Writing a Custom SQL Query
After connecting to your data source, you often have an advanced option to enter a custom SQL query directly. This gives you more control over the initial data pull.
Power BI will still attempt to fold transformations applied after this initial query, as long as they are foldable operations.
The Advanced Method: Using 'View Native Query' from Scratch
For complete control, you can write a full native query from scratch using the
Value.NativeQueryfunction in the Advanced Editor.Crucially, to ensure query folding is enabled with this method, you must explicitly add theÂ
[EnableFolding=true]Â Â flag. Without this flag, folding will not work.
2. Applying Transformations & Verifying Folding
Once you’ve loaded your data into the Power Query Editor, you can begin applying transformations like filtering rows, removing columns, or renaming fields.
As you apply these steps, Power BI constantly tries to translate your changes into a single, optimized query that it sends to the source. This happens behind the scenes and is designed to keep your query efficient.
To verify that folding is still active, right-click on a step in the Applied Steps pane and select View Native Query.Â
The Native Query window will then display the SQL query that Power BI is sending to your data source.
3. Diagnosing Why Query Folding Breaks
A common issue is when a transformation breaks the folding chain, causing Power BI to process all subsequent steps locally. This can drastically reduce performance, especially with large datasets.
The first indicator is that the View Native Query option becomes greyed out. This is a clear signal that the last transformation you applied was not foldable.
The primary reason this happens is that an operation cannot be translated into the source’s native query language. For native queries specifically, this occurs if you forget to add the
[EnableFolding = true]flag in the Advanced Editor.
Without the flag:
Without the EnableFolding flag, folding doesn’t work.
With the flag added properly:
When the flag is added, the View Native Query option becomes available, confirming that folding is working correctly.
4. Quick Tips for Debugging
Inspect the Advanced Editor: If you are using a native query, always confirm that
[EnableFolding = true]is present. Without it, folding may silently fail.Use View Native Query as a Diagnostic Tool: Check it frequently after key transformation steps. If it’s greyed out, you know where to start troubleshooting.
Be Aware of Connector Behavior: Some connectors, like Snowflake, may show View Native Query as enabled even if folding isn’t truly occurring. The most reliable indicator for a native query is the
[EnableFolding]flag.
Why is Query Folding Important in Power Query?
Query folding isn’t just a technical detail; it’s a key contributor to how fast, efficient, and scalable your Power BI reports can be. The ability to push transformations back to the data source can make or break performance, especially with large datasets.
Key Benefits of Query Folding
Boosts Performance and Reduces Load: By sending transformations like filters and joins to the data source, Power BI handles much less data and does far less work. This leads to faster load and refresh times, lower memory usage, and reduced network traffic.
Enables Efficient Modeling: In complex data models with multiple tables and aggregations, query folding ensures these heavy-lifting operations are done at the source where they’re most efficient. This keeps Power BI responsive, even when dealing with millions of records.
Crucial for DirectQuery and Real-Time Solutions: In DirectQuery mode, Power BI sends live queries to the data source. Query folding is essential to ensure these live queries are optimized before they hit the database, preventing delays and making real-time reporting feasible.
When Does Query Folding Occur?
Whether a query folds or not depends on two main factors: the data source and the transformations you apply.
Data Sources That Support Query Folding
Query folding works best with structured sources that can interpret and execute SQL-like queries.
Common Foldable Sources: SQL Server, Snowflake, Amazon Redshift, Google BigQuery, PostgreSQL, and SAP HANA.
Sources That Don’t Support Folding: Flat files like Excel, CSV, and Web connectors, as they lack a query engine.
Common Transformations That Support Query Folding
Many basic transformations can be folded because they have direct equivalents in SQL.
Here are some of the most common foldable transformations:
Filtering rows (
Table.SelectRows)Sorting data (
Table.Sort)Removing columns (
Table.RemoveColumns)Renaming columns (
Table.RenameColumns)Changing data types (when done early in the query)
Merging queries (joins)
Grouping data
Aggregating data (
Table.Group)Expanding related tables or fields
Appending tables (
Union)Selecting top rows
These transformations can often be translated into native SQL commands like WHERE, ORDER BY, JOIN, SELECT, GROUP BY, etc., allowing the source system to execute them before Power BI even loads the data.
Examples of Transformations That Break Query Folding
While Power Query tries to preserve folding as long as possible, certain actions inevitably break it. Once query folding is broken, any subsequent steps in the query chain will also not fold, even if they are individually foldable.
Let’s explore some of the most common transformations that tend to break Query Folding, and why they do so.
Combining Queries from Different Sources: Merging or appending data from two different sources (e.g., a SQL table and an Excel file) will break folding. Since they don’t share a common engine, Power Query must pull all data into memory to perform the operation.
Custom Columns with Complex Logic: Creating a custom column using M functions that don’t have a direct SQL equivalent will break folding. Examples include
Text.MiddleorDate.ToText.Pivoting and Unpivoting: These operations are generally not supported natively by relational databases and must be processed locally by Power Query.
Adding Index Columns: Power Query adds these locally, as most data sources don’t have a native function for generating a sequential row number in this way.
Using
Table.Buffer(): This function forces Power Query to load the entire dataset into memory immediately, which stops all folding.
The Role of Query Folding in Incremental Refresh
Query folding is absolutely critical for an efficient Incremental Refresh policy.
When you configure incremental refresh using the RangeStart and RangeEnd parameters, Power BI must be able to push those date filters back to the source system. This crucial step is only possible if query folding is preserved.
If folding is intact, Power BI generates a native query that retrieves only the filtered data (e.g., “last month’s records”), which drastically reduces refresh time and memory usage. However, if folding breaks, Power BI is forced to pull the entire dataset and filter it locally, which completely defeats the purpose of incremental refresh.
To ensure your setup is efficient, always verify that query folding is working correctly after applying your RangeStart and RangeEnd parameters. You can do this by using the View Native Query option.
Limitations
While query folding is a powerful tool, it’s not without its limits. Be aware of these scenarios where folding may be disabled:
Flat-file Sources: Query folding is not supported with sources like Excel, CSV, JSON, or Web APIs, as they lack a query engine.
Privacy Levels: Combining data from sources with different privacy levels can disable query folding.
Poorly Optimized Queries: In some cases, Power Query may produce inefficient SQL for complex transformations, which can result in slow performance despite folding being applied.
View Native Query Availability: This option isn’t available for all connectors, which may limit your ability to verify folding status visually.
Closing
Understanding and leveraging Query Folding is key to building efficient and high-performing Power BI solutions. It allows Power Query to delegate heavy lifting to the data source, resulting in faster refreshes, reduced memory usage, and improved overall performance, especially when working with large datasets or implementing incremental refresh. However, it’s equally important to be aware of where folding might break and which scenarios or transformations limit its effectiveness. By designing queries with these factors in mind, you can make your data models more scalable, reliable, and better aligned with enterprise-grade reporting needs.
Ready to boost Power BI performance?
phData’s experts can help you leverage query folding to streamline reporting, boost performance, and eliminate sluggish queries.Â
Reach out for tailored guidance and discover how your organization can benefit from optimized Power BI solutions.
FAQs
How do I know if Query Folding is happening in my Power Query?
The easiest way is to use the View Native Query option in Power Query. If it’s enabled, folding is happening up to that step. If it’s greyed out, the folding has broken.
Can Query Folding be applied to all types of data sources in Power BI?
No, not all data sources support Query Folding. It works best with relational databases that can execute SQL-like queries, such as SQL Server, Snowflake, and PostgreSQL. It does not apply to flat files like Excel, CSV, or other file-based sources.
What happens if Query Folding breaks in my Power Query?
Power Query will process all subsequent transformations locally. This can lead to slower performance and higher memory usage, especially with large datasets.
