Previously, I’ve written a blog post about Top N & query folding to improve report building. In this blog post I explained how query folding groups Power Query steps together and execute at the data source. Matt Allington also wrote a great blog with further explanation on how query folding works.

While working on projects, It is always my goal to keep query folding active as long as possible. Simply because it improves my dataset refresh timings and reduces the load executed on Power BI side.

While working on a project last week, I found something interesting in default Power BI functionality which breaks query folding, but can be avoided by slightly changing the Power Query code yourself or picking another option which results in exactly the same. So, this week a short blog post to make you aware of things which might brake query folding.

The case

To give an example, I’ve used the Adventure works database in an Azure SQL database. As soon as I import for example the Sales Order Header table, I can apply some steps to transform the table to something I want. In this case I simply removed and renamed some columns. So far all my steps are folded back to the native source.

Looking at the Due Date column, I see that this included Date and Time, where time is useless since it is the same for every row (00:00:00). Imagine that I want to get rid of the time and only keep the date. In order to do this, we have a few options. Two of them written down below:

The first option could be the following:

1. Select the column.
2. Go to the Home section in the top ribbon.
3. Change the data type to date.

Marking a column as a date, doesn’t break query folding. So far, so good! Another approach could be the following:

1. Select the column.
2. Go to the transform section in the top ribbon.
3. Click on the date drop down, and select Date Only.

Query folding breaks!

Even though the result of both above described approaches is exactly the same, the second option will break query folding! Looking at the Power Query code which is generated in the second approach, this looks like the following:

Table.TransformColumns(#"Rename Columns",{{"Due Date", DateTime.Date, type date}})

In order to transform the DateTime to a Date column, the DateTime.Date function is performed. Luckily Power Query has different functions to transform a datetime column to a date. By slightly changing the code we can get query folding working again. Using Date.From will do the trick! Your Power Query code will look like he following:

Table.TransformColumns(#"Rename Columns",{{"Due Date", Date.From, type date}})

However, both options result in the same. Be aware of the little differences which might have a big impact in the end. Always try to let query folding work for you as long as possible!