When working with large datasets in Power Query, optimizing performance is essential to ensure fast, efficient data processing. One powerful feature that can help is query folding.

Query folding allows Power Query to offload transformations to the data source, minimizing the amount of data processed locally and improving query speed. In this guide, we’ll explain what query folding is, how it works, and best practices for maximizing its benefits.

1. What is Query Folding?

Query folding is the process by which Power Query translates its transformations (e.g., filters, joins, and aggregations) into SQL-like queries that are sent to the data source. When query folding is enabled, the data source performs these transformations instead of Power Query, leading to faster and more efficient processing.

Example:

If you connect Power BI to a SQL Server table and apply a filter in Power Query, query folding pushes that filter back to SQL Server so that only the filtered data is returned. This reduces the amount of data transferred and speeds up the query.

2. Why Query Folding Matters

Query folding can significantly improve performance by:

✅ Reducing Data Transfer: Only the necessary data is sent from the source to Power Query.
✅ Improving Processing Speed: Offloading transformations to the data source reduces the local processing load.
✅ Enhancing Query Efficiency: The data source often has optimized engines (like SQL) for handling large datasets.

3. How to Identify Query Folding

Checking Query Folding Status:

You can check if query folding is happening by following these steps in Power Query:

  1. Right-click on any step in the “Applied Steps” pane.
  2. Select View Native Query from the context menu.
    • If the option is available, query folding is enabled, and you can view the SQL query that Power Query has generated.
    • If the option is grayed out, query folding is not occurring.

4. Common Transformations That Support Query Folding

Not all transformations support query folding. Here are some common ones that typically fold back to the data source:

✅ Supported Transformations:

  • Filtering rows: e.g., Table.SelectRows()
  • Removing columns: e.g., Table.RemoveColumns()
  • Changing data types: (for SQL-compatible types)
  • Sorting and grouping:
  • Joins and merges:

❌ Transformations That Break Query Folding:

  • Adding custom columns with complex logic.
  • Applying certain M language functions that Power Query cannot translate to SQL.
  • Performing unstructured data transformations (like splitting columns or pivoting data).

5. Best Practices for Query Folding

To maximize the benefits of query folding, follow these best practices:

✅ Apply Filters Early:

Apply filters as early as possible in your query to reduce the amount of data retrieved from the source.

✅ Minimize Non-Folding Steps:

Keep folding-compatible steps at the beginning of your query and avoid non-folding operations that might break the folding chain.

✅ Use Native SQL Queries (If Necessary):

If query folding isn’t working as expected, consider writing your own SQL query using the Advanced Editor in Power Query to manually push transformations to the data source.

6. Example: Query Folding in Action

Scenario: Filtering Sales Data by Region

Suppose you’re working with a SQL Server database that contains millions of sales records. You want to filter the data to include only sales from the “North” region and remove unnecessary columns.

Step 1: Connect Power Query to the SQL Server table.
Step 2: Apply the following transformations:

  • Filter rows where [Region] = “North”.
  • Remove unnecessary columns (e.g., customer address, phone number).

When you right-click on the filter step and select View Native Query, you’ll see the following SQL query:

sqlCopyEditSELECT [SalesID], [Region], [SalesAmount]  
FROM [SalesData]  
WHERE [Region] = 'North'

This query is executed by SQL Server, and only the filtered, streamlined data is sent back to Power Query, resulting in faster processing.

7. When Query Folding Isn’t Possible

Sometimes, query folding may not be possible due to the type of transformations applied or the limitations of the data source. In such cases, consider the following alternatives:

  • Simplify Your Query: Reduce the complexity of transformations to enable folding.
  • Use Native SQL Queries: Write a custom SQL query to handle complex transformations at the source.
  • Buffer Large Tables: Use Table.Buffer() to optimize local processing (as a last resort).

Conclusion

Query folding is a powerful technique for improving Power Query performance by offloading data transformations to the source. By understanding how query folding works and following best practices, you can optimize your data workflows, reduce processing time, and enhance the efficiency of your Power BI reports.