Query Folding in Power BI: Everything You Need to Know

Query folding in Power BI is one of the most powerful yet often overlooked feature. It can significantly improve performance, reduce data transfer, and optimize your data refresh process — especially when dealing with a fair amount of data. If you’re working with large datasets, understanding query folding is essential.

What is Query Folding in Power BI?

Query folding is the process where Power BI pushes data transformation steps back to the data source instead of processing them locally. This is particularly useful when handling a fair amount of data, as it minimizes the load on Power BI.

For example, if you connect to a SQL database and apply filters, sorting, or column removals in Power Query, Power BI can generate a single SQL query that performs these operations at the database level rather than fetching unnecessary data and processing it in Power BI.

🗝️ Key Benefits of Query Folding:

    • Faster Performance: Reduces the data transferred between the source and Power BI.
    • Lower Resource Usage: Offloads processing to the database server, which is especially helpful when dealing with records.
    • Efficient Incremental Refresh: Helps in scenarios where only new or changed data needs to be loaded, saving processing time.

Why is Query Folding Important?

A. Improved Refresh Speeds

When transformations are folded back to the source, Power BI retrieves only the necessary data, which is crucial when working with a fair amount of records.

B. Reduced Network Load

Instead of pulling certain rows and then filtering locally, Power BI sends an optimized query like:

				
					SELECT col1, col2 FROM table WHERE condition ORDER BY col1

This minimizes data transfer and improves efficiency.

C. Better Scalability

For datasets containing a fair amount of data, query folding ensures that Power BI doesn’t run out of memory by processing everything locally.

Flowchart Explanation

Query Folding in Power BI (Flowchart)
  1. Starting Point Power BI Data Import: The process begins when you connect Power BI to a data source and start importing data.
  2. First Decision (Diamond Shape)
    • “Source Type?”: Checks what kind of data source you’re using:
      • SQL Database (supports query folding) ➔ Continue to optimization path
      • Flat Files (Excel, CSV) ➔ Immediately jumps to “No Folding Possible” (since these can’t push processing back to source)
  3. Optimization Path (Green Highlights)
    • For SQL sources that support folding:
      • “Initial Query Sent”: Power BI establishes connection
      • “Power Query Transformations”: Where you apply filters, remove columns, etc.
      • “Query Folding Check”: Power BI determines if your transformations can be translated to source language (like SQL)
  4. Broken Folding Path (Red Highlights)
    • If Folding FAILS:
      • “Local Processing” (Red box): Power BI has to:
        • “Full Data Transfer”: Pull ALL raw data first
        • Process everything locally (slower)
        • Results in “Slow Refresh” (Red outcome)

How to Check if Query Folding is Working

To verify if your transformations are being folded:

    1. Right-click on a step in Power Query → If “View Native Query” is enabled, query folding is working.
    2. If it’s grayed out, folding is broken after that step.

Example:

    • If you filter rows in SQL and see the WHERE clause in the native query, folding is active.
    • If you add a custom column with complex logic while handling data, folding may break.

Transformations That Support Query Folding

Most basic operations can be folded, which is helpful when managing a certain data:

✅ Filtering rows (WHERE clause)
✅ Removing/renaming columns (SELECT statement)
✅ Sorting data (ORDER BY)
✅ Grouping & aggregations (GROUP BY)
✅ Joining tables from the same source (JOIN operations)

When Does Query Folding Break?

Certain transformations prevent folding, especially when dealing with a fair amount of complex operations:

❌Custom Columns with Complex M functions (e.g., Text.Length, Date.AddDays)
❌ Merging data from different sources (e.g., SQL + Excel)
❌ Adding index columns
❌ Pivoting/unpivoting in some cases
❌ Using Table.Buffer() (forces local processing)

Pro Tip: If folding breaks while processing a fair amount of data, try moving filter steps earlier in the query.

Best Practices for Query Folding

    1. Apply Filters Early: Filter data as soon as possible to reduce the fair amount of unnecessary data loaded.
    2. Avoid Unnecessary Custom Columns: If possible, perform calculations in SQL or use simple operations that fold, especially when handling a certain amount of records.
    3. Use SQL Views or Stored Procedures: For complex logic, push transformations to the database via views or procedures to maintain folding efficiency.
    4. Monitor with Performance Analyzer: Check query execution times in Power BI Desktop to identify bottlenecks, particularly when working with data.
    5. Prefer DirectQuery for Large Datasets: If folding is critical, DirectQuery ensures all processing happens at the source, which is ideal for datasets with a certain amount of records.

Conclusion

Query folding is a game-changer for Power BI performance, especially when dealing with a fair amount of data. By pushing transformations back to the data source, you reduce load times, minimize data transfer, and improve efficiency. Always check if your steps fold and optimize your queries accordingly.

Need help? Test your queries by right-clicking steps to see if “View Native Query” is available. If not, revisit your transformations to ensure maximum folding efficiency—especially when handling a fair amount of records.

For more Power BI tips, stay tuned! 🚀

Scroll to Top