Though Power Query's query folding is functionally limited when dealing with elaborate data sources, the following can mitigate the issue.
Focus on Basic Transformations: Custom functions or even multiple joins may involve complex transformations that affect the chain's folding. Rather, look for basic steps that can easily be returned to the Source. For instance, row filtering, data grouping, and data type changes are all query folding-supported operations; therefore, these errors should be carried out before incurring any complex steps.
Incorporate Views or Stored Procedures: If a database is in use, custom stores or views can be created at the data source level. This helps eliminate complex transformations and logic in the relational database engine, achieving query folding. Power Query then takes advantage of the view or procedure, which results in better performance.
Remember to Check the Native Query Option: The "Native Query" option in Power Query is available for certain databases, such as SQL Server. This feature allows you to write code SQL, and its execution takes place outside of Power Query, which also minimizes the restrictions regarding query folding. One can also consider writing a separate optimization query to enhance the efficiency of the operation performed within Source.
Dismantle Difficult Queries Feel free to split a very complicated query into different parts.
For instance, load the data boy, performing minimum transformations first, and progressively, in separate queries, perform more complex steps. This permits power queries to fold in simpler transformations while complex ones are applied in stages.
Tracking Power Query Query Folding: If you would like to know if the transformation is being folded back to the source, you can write a step in the Applied Steps pane and choose ‘View Native Query”. If this option is enabled, it means that query folding is possible for that step. If this option is greyed out, it implies query folding is for that step, not there. In these instances, either modify your transformations or make them less complex.
Employ Smart Data Types at the Very Start Optimizing their data types at the earliest possible stage helps to ensure query folding can take place. Changing data type once expensive transformations have been carried out is inadvisable as this will free rehabilitation from being carried out.
Eliminate Wasted Actions: Introducing excessive transformations in Power Query can sometimes hinder query folding. Minimize unnecessary processes and refrain from taking intermediate steps that can be managed at the data source level.
When these techniques are incorporated into your Power Query, however, they help minimize the number of transformations that are not folded back to the data source to enhance performance.
