Power BI: Performance Optimisation and Query Folding

Optimising Power Query performance is important when handling large datasets and it can turn a working data transformation into a swift and efficient one. By focusing on Query folding, minimising resource-intensive operations, and writing efficient M code, you can significantly enhance your data processing.

What is Query Folding

After the data transformation’s steps are created and are ready to run, query folding is when Power Query translates these steps into the query language native to the data source. This means that instead of pilling all the data into Power Query and carrying out the transformations, Power Query pushes those transformations onto the data source itself whenever possible.

Passing some data transformations to the data source to filter out unnecessary data means that less data is loaded into Power Query that requires processing. This is similar to how Power Query filters the dataset before it’s loaded into the Power BI data model.

Enabling Query Folding

• Use supported transformations - Rely on transformations that can be translated easily into the data source’s native query language, For example, filtering rows and selecting specific columns are operations that most databases can handle directly.

• Order the steps - In the order of steps in the transformation, put the translatable steps first because when a non-translatable step occurs the following steps might not be folded back to the data source so all translatable steps need to be before the first non-translatable step.

Query Folding Pizzeria

A good analogy to explain query folding is to compare it to making a pizza. You’re a pizza chef and you know how to spin pizza dough, stuff the crusts, spread the toppings well etc. However you get your ingredients from someone else. Now let’s say that the main steps to making the pizza is cutting salami and vegetables, grating cheese, spinning the dough and laying the toppings on before baking. You can do all of these things yourself however if you passed the ingredient preparation steps to the food supplier and completing the rest of the steps afterwards such as spinning the dough and laying the toppings then you end up having to do more work in the end.