Power Platform Tidbits #7 – Optimize for server-side execution (folding) when adding columns

Power query can take some of the relational transformations and ask the data source to take care of them. This means that the transformations – like grouping, filtering, adding columns and more – can run at the data source, where they are more efficient. This also avoids downloading huge amounts of data and accelerate the execution. In Power BI terms, this is called folding.

When writing a Power query you need to make sure every step that can be passed to the data source is passed to the data source. If you pay attention to the Applied steps, when a green vertical line is displayed besides a step, it mean that the step is passed to the data source. For the following picture shows that the first steps, until “Kept top rows” are passed to the data source.

Please note that once you add a step that cannot be passed to the data source, any step that come after will not be passed to the data source. This means as a general rule of thumb, you should try to apply all foldable steps in the beginning when you can.

You should also try to pay attention to when you are mixing multiple functions in a step. You might notice that when you separate them, a part can fold to the data source. Sometimes it can be tricky to spot them, specially when the UI is mixing them behind the scene. Let’s take an example to make it clear.

For example when you add a new column and define its data type through the UI, Power BI will mix two functions Table.AddColumn and Table.TransformColumnTypes, even though it’s just one column and Table.AddColumn is capable of defining the column type. See for yourself.

Now the issue is even though Table.AddColumn is a foldable function, the Table.TransformColumnTypes isn’t. To fix this issue, you either need to separate them is different steps or just rely on Table.AddColumn to both add the column and define the type. In the above example we must change:

Table.TransformColumnTypes(Table.AddColumn(#"Selected columns", "Custom", each 1000), {{"Custom", Int64.Type}})

To:

Table.AddColumn(#"Selected columns", "Custom", each 1000, Int64.Type)

And voila: