Query Folding in Power BI

Introduction

Query folding in Power BI is a powerful feature that significantly enhances data processing, efficiency and query performance. This blog post will explore the concept of query folding, its implementation in Power BI, and the benefits it offers for data analysis and reporting.

What is Query Folding?

Query folding refers to the process by which Power BI pushes data transformation operations back to the data source. This leveraging the source system’s native capabilities. Instead of performing transformations within Power BI, query folding ensures that these operations are applied during the initial data retrieval stage. This reduces the volume of data transferred to Power BI and optimizing overall performance.

How Query Folding Works in Power BI?

Power BI automatically enables query folding for supported data sources, such as relational databases like SQL Server and Oracle. The query folding mechanism translates Power Query transformations into native queries that can be executed by the source database. This process offloads processing to the database itself, minimizing the amount of data moved to Power BI and making operations faster and more scalable.

The documentation from Microsoft is found here.

Benefits of Query Folding

1. Improved Performance: By pushing transformations to the data source, query folding reduces data transfer and leverages the source system’s query optimization capabilities.

2. Reduced Memory Consumption: Query folding minimizes the amount of data loaded into Power BI’s memory, allowing for more efficient resource utilisation.

3. Enhanced Scalability: With query folding, Power BI can handle larger datasets more effectively, as much of the processing occurs at the data source level.

4. Faster Data Refresh: Query folding optimizes the data refresh process, especially when dealing with large amounts of data

Types of Query Folding

1. Complete Query Folding: All query transformations are pushed back to the data source, with minimal processing occurring in Power BI.

2. Partial Query Folding: Only a subset of transformations is executed at the data source, with the remaining operations performed in Power BI.

3. No Query Folding: When transformations cannot be translated to the native query language of the data source, all processing occurs within Power BI.

Implementing Query Folding in Power BI

To take advantage of query folding in Power BI:

1. Use supported data sources that allow query folding, such as SQL Server or Oracle databases.

2. Design queries with transformations that can be easily translated to native database operations.

3. Leverage native SQL queries when possible to ensure optimal query folding.

4. Monitor query folding using Power BI’s built-in tools and indicators.

Limitations and Considerations

While query folding offers significant benefits, it’s important to be aware of its limitations:

1. Not all data sources support query folding.

2. Complex transformations may not be foldable, requiring processing within Power BI.

3. Some Power Query functions and custom M code may prevent query folding.

What Stops Power BI Query Folding

Certain actions and transformations can prevent query folding, which forces Power Query to handle those transformations locally. Here are some common actions that stop query folding in Power BI:

1. Unsupported Transformations

• Row Numbering: Adding row numbers generally stops folding because most databases don’t support row numbers in the same way as Power Query.
• Changing Data Types: Applying certain data type changes, especially on non-database-supported types (like complex data types), can stop folding.
• Adding Calculated Columns: Complex calculations or custom columns, especially those that reference other calculated columns, can prevent folding.

2. Use of Non-Foldable M Functions

• Some M functions don’t have direct SQL equivalents, and Power Query can’t push them back to the data source. Examples include:
  • Table.Buffer(): This function caches the table in memory, which stops folding.
  • Table.AddIndexColumn(): Indexing is often not supported in database backends.
  • Table.Combine(): Combining tables is generally not foldable if the tables are from different sources or have incompatible schemas.

3. Conditional and Complex Logic

• Custom Functions: Using custom M functions within queries can prevent folding because the logic often can’t be translated to SQL.
• Conditional Columns: Creating conditional columns based on complex logic or calculated fields can stop folding.
• Nested or Multiple Joins: If joins involve complex transformations, folding may be prevented, especially if data sources are not optimized for such joins.

4. Referencing Previous Steps

• Multiple Queries with Referencing: If one query references another query that is already loaded into memory, folding might stop in the second query.
• Duplicating Queries: Sometimes, duplicating queries can interrupt folding, depending on the transformations applied in each query.

5. Using Excel or Unsupported Sources

• When you use sources that don’t support SQL (like Excel, CSV files, or text files), query folding cannot occur. Power BI can only fold queries back to sources that support SQL queries.

6. Applying Actions Out of Order

• The order of transformations can impact folding. For instance, filtering rows early in the query often allows folding to continue, but if a complex transformation is applied before filtering, it may break folding.

7. Merging Queries from Different Sources

• Merging tables from different sources (like joining a SQL table with an Excel table) stops query folding since Power Query must pull the data into memory to complete the operation.

Conclusion

Query folding in Power BI is a crucial feature that optimizes data processing and enhances overall performance. By understanding and implementing query folding effectively, data analysts and BI professionals can unlock the full potential of their data sources and deliver faster, more efficient data analysis and reporting.

Please find more tips and update from our blog here.