It's a feature that enables complex data preparation operations to push themselves to the source database without being executed in Power BI itself. This helps improve performance and scalability since the processing is done on the database. Here's how to effectively use query folding for complex transformations:
Source your Transformed Data from an Accessible Data Source.
Most importantly, check if your data source folds. The majority of relational databases nowadays are SQL Server, Oracle, PostgreSQL, etc., allowing Power Query to convert transformations into native queries. It makes way for enabling the function of query folding.
Transformations Primed for Folding
Foldable Transformations: Filtering, Grouping, Merging, and Joining. Apply steps in Power Query while you watch if the fold is still on. Right-click on a step and select View Native Query. You will see a query when it is still folding; if not, it won't be doing it anymore, and the processing will take place locally.
Reduce Steps Breaking Folding
Avoid using transformations that normally tend to break query folding, such as some user-defined M code or data type conversion. Structure query logic so that the expensive non-foldable operations are performed after foldable steps to maximize processing offload.
Use Query Diagnostics
Use the power query diagnostic tool to identify and monitor the folding of DAX. This will confirm that the translation of your complex transformation goes into a more efficient query executed on the database source.
You can significantly minimize Power BI resource loads, reduce refresh times, and query much bigger datasets.
