Importance of Query folding and good practices

Query Folding is the capability of Power Query to delegate some of the transformations to the source to improve performance. In this post you will see its importance and good practices guide to achieve it.

According to official documentation the following data sources support query folding:

– Relational databases (SQL Server, Oracle, etc.).

-OData sources (including SharePoint lists).

– Active Directory.

Data sources like flat files (Excel, CSV, txt, etc.), blobs, and web usually do not support it. Check here the list of transformations that prevent query folding.

You can check if the query folding occurs by right-clicking on the last applied steps and validating if View Native Query option is enabled, when the View Native Query option is grayed out, it means that this specific step or transformation is executed with local resources.

Query Folding importance:

– Tables in import mode will have a lower resource utilization and faster refresh time.

– Direct Query and Dual storage mode table only admit Power Query queries that can be folded.

– To be able to configure the incremental refresh in a table, it is essential that the query folding can be achieved.

Query folding good practices:

– Fully Folding: The preferred scenario is to achieve a query folding fully, that is the source executes all required transformations and returns to Power BI the desired result set.

– Partial Folding: Place steps that support query folding at the beginning and move the steps that prevent query folding later because these transformations will be loaded into the local cache and processed locally by Power BI engine.

The idea is to maximize the performance getting as much query folding as possible. However, the recommendation is to avoid this option whenever you can.

• Native Query: If you use a native query include all the logic of the required transformations there, because any other transformations applied to the results of this function will be processed with local resources.
• Apply the transformations in the data source: When you identify that your query requires a large number of steps that can’t be folded, apply the transformations in the data source using a database view or by physically preparing and materializing data.

I recommend you create a view because it can provide you with multiple advantages over regular tables. Check this interesting article about that.

Conclusion

When you are working with a relational data source (such as SQL Server) always check the query folding. If most of the steps do not support it, use another approach for the transformations.

Not applying these recommendations in your data model could exponentially increase resource usage and refresh time and will prevent you from using Direct query or Dual storage mode and apply incremental refresh policies in your data model.