Don’t let date columns ruin query folding in Power BI

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.

QueryFolding
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!

3 thoughts on “Don’t let date columns ruin query folding in Power BI

  1. Pingback: Date Columns and Query Folding in Power BI – Curated SQL

  2. Daniel Osio

    Hi! Thanks for the info. I hope yo can help me with this problem… I have a SAP BW source, where the date column is in “dd.mm.yyy” format, and Power BI recognizes as text. When I change it to datetime format using your method or with the Power BI ribbon option, the query folding breaks! It’s another way to do it?

    Like

    1. Hey Daniel,
      I’ve heard this before, as SAP BW is not the most supporting data source to use.

      What crossed my mind the the following;
      Try to create Power Query steps to see if you can extract the day, like extract 2 characters. Same for month and year. Afterwards you can concatenate them again to a date in a custom column. I would expect all these steps to be folded to T-SQL statements.

      Secondly, be aware that “view native query” is only an indicator, but sometimes query folding still applies although view native query is not available any more. Try profiler tools or the logging of SAP to check what exact query is fired at the system.

      Hope this helps
      Marc

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s