If you’re using Power BI, you’ve likely come across the term query folding—a performance-boosting feature within Power Query. In this blog, we’ll break down the meaning of query folding, how it works, provide a real-world query folding example in Power BI, compare it with native queries, and help you spot query folding indicators easily. Whether you’re working with dataflows, Databricks, or a basic SQL source, this guide has you covered.
What is Query Folding in Power Query?
Query folding in Power BI is the process where Power Query translates your transformation steps into a native query that gets executed directly on the data source. Instead of pulling all data and processing it locally, Power Query tries to “fold” as many steps as possible into one optimized backend query.
For example, if you’re filtering rows or removing columns from a SQL database table, Power Query will try to include those transformations directly in the SQL command it sends to the server. This results in faster, more efficient data loads.
Query Folding Meaning – In Simple Terms
Think of query folding as “doing the work where the data lives.” Instead of bringing a million rows to Power BI and filtering them, Power Query sends instructions to the data source (like SQL Server, Databricks, or Snowflake) to only return what you actually need. This process saves time, memory, and system resources.
Featured
Query Folding Example in Power BI
Let’s take a quick example:
- You connect to a SQL table with customer data.
- You apply a filter: Country = ‘India’
- You remove unnecessary columns like address or phone number.
Now, if you right-click a transformation step and select View Native Query, Power Query will show you the exact SQL command it’s sending to the database. This confirms that query folding is happening—your steps are executed at the source level.
But if you then add a custom column that changes text casing (like converting names to uppercase), Power Query might stop folding. That’s because the transformation isn’t natively supported in SQL, so it falls back to local processing.
Query Folding Indicators in Power BI
Power Query gives you visual cues to know whether folding is happening:
- A small icon appears next to each transformation step.
- Green tick = Step is folding.
- Grey or missing icon = Step isn’t folding.
- “View Native Query” enabled = Folding is active.
- “View Native Query” greyed out = Folding has been broken.
You can use these query folding indicators to troubleshoot and rearrange your steps for optimal performance.
Query Folding vs Native Query
Here’s the difference:
- Query folding: Automatically generated queries created by Power Query based on your transformations.
- Native query: Manually written SQL query that you paste into Power Query.
While native queries give you more control, they usually prevent further query folding. Once you start with a native query, additional transformations likely won’t fold. That’s why query folding is generally preferred for performance and refresh efficiency.
Query Folding in Dataflows and Databricks
- Query folding in Dataflows works much like Power BI Desktop. When building Power Query logic in cloud-based dataflows, the same folding indicators appear. Aim to keep as many transformations as possible foldable to improve refresh speeds.
- Query folding in Databricks depends on the connector you use. Some connectors allow folding (like Databricks.Query), while others don’t support it fully. Always check the folding behavior for best results.
When Query Folding Fails
There are a few reasons why query folding may stop working:
- Using unsupported custom functions or complex M code.
- Combining different data sources (e.g., SQL + Excel).
- Applying transformations in a non-optimal order (e.g., text manipulation before filtering).
Once folding is broken, Power Query pulls all the data and does the processing locally, which can slow things down significantly.
Best Practices to Maximize Query Folding
| Tip | Description |
|---|---|
| Start with filter steps | Always filter data as early as possible. |
| Remove unnecessary columns | Project only the fields you need. |
| Avoid early custom columns | Postpone custom M logic until the end. |
| Use compatible connectors | Prefer SQL, Databricks.Query, etc., which support folding. |
Final Thoughts
Understanding and leveraging query folding in Power BI and Power Query can significantly boost performance and reduce refresh times. From query folding indicators to real-world use in dataflows or Databricks, this concept is crucial for any serious Power BI developer.
Remember, it’s not just about building reports—it’s about building them efficiently. Use folding to let your data source do the heavy lifting.