Nowadays many systems such as cloud services and IOT devices that work globally prefer to use the UTC time zone to be consistent across different countries. When it comes to reporting however, the end user likes to see the datetime values in their own local time zone. This post shows you how to convert datetime values from UTC to local time.

Date/Time – Date/Time/TimeZone

There are 2 datatypes in Power Query that are used for storing date and time values:

• Date/Time 
• Date/Time/TimeZone

The latter type has the addition of a timezone which shows the difference to UTC time in hours and minutes. Some places around the world have 2 different time zones due to daylight saving.   For example Sydney Australia in winter uses Australian Eastern Standard Time (AEST) which is 10 hours ahead of UTC and in summer it uses Australian Eastern Daylight Time(AEDT) which is 11 hours ahead of UTC. Below, you can see that LocalDateTimeZone column that is Date/Time/Timezone includes +11:00 which is the time difference to UTC.

How to convert UTC to local time

For this exercise I create a simple table with 2 columns. TimeGenerated is the Date/Time/TimeZone column which contains the UTC time and I am going to change it to local time.    

Let’s create a duplicate of TimeGenerated and change its data type to DateTime:

The data in the duplicated column has now changed to local time(+11:00) and it looks like we have completed the task!

But this conversion has used my machine’s local time zone, if I published the report to Power BI Service and refresh the data there instead, the data model would contain the times at UTC because the Power BI Service uses UTC as the default time zone. The correct solution is using the SwitchZone function and creating a custom column to calculate the local time:

This custom column works in both Power BI Desktop and Service: 

Including Daylight Saving Time

In the previous step we added +11 hours to UTC to have Sydney time but this is only for the summer when daylight saving time is applied. In the winter the difference is 10 hours. For handling this, we must find out whether the date is in DST period or not and add the corresponding hours to UTC.

First create a table named DST and enter start and end time of daylight saving times for the years your data belongs to:

Then create a custom function named DSTCheck which get a UTC datetime value as an input parameter and returns 1 if the date in the DTS period and 0 if it is not. 

The last part is changing the formula of the custom column and adding the result of the function to the time difference (+10):