What time is it right now for you? We might share the same time zone, but that is usually not the case with worldwide operations.
If I say, let’s meet tomorrow at 8am. Will that be your 8am? Or will that be my 8am?
I feel like I should’ve posted this blog post a long time ago, but it’s better later than never. (maybe it was a time zone difference situaton? 🙂 )
In Power BI you can have date or date+time fields/columns once they’re loaded into your Data Model, but prior to loading them (inside the Power Query Editor) you can actually have them as date timezone, which is a specific data type that only holds date and time information, but also the time zone.
Let’s have a quick scenario. Ken is in Canada, Miguel is in Panama – let’s define a set of appointments specific for both time zones.
I’ve been working with Ken Puls for quite a few years now and he’s usually around 2 hours behind me, so the systems that we use are under a specific timezone for some and in a different timezone for others, which we need to “switch” to a unified timezone.
Datetimezone data type in Power BI / Power Query
I have these dates that Ken sent my way specific for a set of orders that came into our system. I’m trying to align all of my dates in my model to be under my current timezone in Panama, but Ken sent me these in his own timezone:
How do I transform these datetimezone values into my local time zone?
Easies way to transform to local time zone in Power BI / Power Query
The easies way to accomplish this is to actually go into either the Transform tab or the Add Column tab and go into the Date & Time Group.
Once in that group, select the option for time and you’ll see from the dropdown a choice for local time
And the result of that operation will yield something like this.
(note that I used the option from the Add Column tab)
Notice one importing aspect about this approach from the formula bar and that is that it uses the DateTimeZone.ToLocal function which only requires a value with the datetimezone data type.
The caveat here is that it uses the local time from the regional settings of my machine, meaning that if I was to use a machine that had different regional settings, it wouldn’t yield the correct result.
This begs the question; how do I explicitly tell Power Query to always convert the value to a specific time zone?
Recommended way to convert time zones in Power BI / Power Query
This one requires you to use a specific function, but it’s quite a simple function called DateTimeZone.SwitchZone which, in comparison to the DateTimeZone.ToLocal, only adds a second argument where you can input (as a number) the correct time zone to which you want to “switch” your original datetimezone value.
The result of that will look like this:
When creating a Data Model with Power BI, it is recommended that your dates are on the same time zone, so that you don’t have to deal with multiple timelines which could get messy, complicated and yield not intuitive results in the end.
The best way to accomplish this is to do a full assessment of your data sources and make sure that all of them have the same time zone. If this isn’t the case and you can’t change them to be on the same time zone, then you can “switch” the time zone once it lands on the Power Query window.
One SUPER important thing to take in consideration is that if you plan to publish this query to the Power BI Service, the actual tenant itself could be on a different time zone than the one that you’re in, so it is recommended that you use DateTimeZone.SwitchZone.
Once your data lands on the Power BI Data Model, the time zone data is completely stripped off the value and what you see on the Data view is just a date or date time:
So it is extremely important that you make sure that everything is working correctly in Power Query as that’s the only way to tell if the time zone is correct or not.
What if my data is not set as a datetimezone?
This is often the case with simple data sources. These simple data sources do not provide more information other than the data as either date or date+time. What can you do in these cases?
The first thing is to do is to make sure that you’re dealing with at least a datetime data type. Once you have your values as datetime, you can create a new column using the following formula:
The formula that does the magic is DateTime.AddZone which adds a time zone to your datetime value. You pass your datetime as your first argument and set the time zone as your second argument.
Once you set your value as a datetimezone data type, you can go ahead and “switch” the timezone to whatever timezone using the methods described previously in this article.
Having the correct date and time is EXTREMELY important.
Let me know what you think about these methods! Do you have any scenarios that are closely related to time zones but not covered in this post? Let me know in the comments below.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.