Data From Different TimeZones

A friend of mine emailed yesterday asking how to compare data from different timezones.  With how good the UI is in Power Query, you’d think this would be easy.  Unfortunately it’s a bit less than that, so I thought it would make a good example for today’s post.

Background

Let’s assume that we’ve got two columns of data; an Order Date and a Shipping Date.  We’d like to work out the number of days it took to ship our order.  Easy enough, we just need to subtract one from the other… except… the system that holds the Order Date reports it in UTC +0:00, and the shipping date is done from my home time zone (UTC –7:00).

The data table we’re starting with looks like this:

image

And you can download a copy of the workbook from my OneDrive here if you’d like to follow along.

Avoiding Temptation

So the first thing to do is pull the data in to Power Query.  So I clicked in the table, went to the Power Query tab, and chose From Table.  At this point we’re greeted with a nice table, and our first temptation is to go directly to the Transform tab and set the Data Type to Date/Time/Timezone:

image

And herein lies a problem.  The system has forced my local TimeZone on the data.  As specified in the initial problem, I need this to carry a UTC +0:00 distinction.

It’s a shame that there is no intermediate step here (how often do I ask for MORE clicks?) which allowed you to specify WHICH TimeZone.  If you’re into working with data from different regions (I.e. this feature), I’d don’t think I’m venturing out on a limb to say that this is pretty important.

To further complicate things, that is the extent of the TimeZone functionality in the UI.  And that’s not going to help us.  So let’s knock off the “Changed Type” step and look at this another way.

Using M to Deal with Data From Different TimeZones

The secret to making this work is to take explicit control of the time zone settings using some Power Query M functions.  It’s not as hard as it sounds.  In fact, we’re only going to use two in this example:

  • DateTime.AddZone to add a time zone to a DateTime data type
  • DateTimeZone.SwitchZone to convert from one time zone to another

I discovered both of these functions by searching the Power Query formula categories article on Microsoft’s site.

Forcing a DateTime to a Specific Time Zone

So we’re currently looking at this data in Power Query:

image

Let’s create a new column to convert the OrderDate:

  • Add Column –> Add Custom Column
    • Name:  Order Date (UTC +0:00)
    • Formula:  =DateTime.AddZone([OrderDate],0)

The secret here is in the last parameter, as we get to specify the time zone.  Since we know these dates/times come out of our system in UTC +0:00, we’re good to not add anything to it.  The result is shown below:

image

Converting a DateTime to a Different Time Zone

Now, in order to be able to compare our DateTimes easily, we want them both to be based in our own time zone.  Since my business works in UTC –7:00, I really want my Order Date represented in that time zone as well.  So let’s convert it.

  • Add Column –> Add Custom Column
    • Name:  Order Date (UTC -7:00)
    • Formula:  =DateTimeZone.SwitchZone([#"OrderDate (UTC +0:00)"],-7)

SNAGHTML1f066319

Beautiful.

Just a note here… It may have been tempting to force this data to UTC –7:00 when we added the time zone above, but that would have assigned the date based in the wrong time zone.  I.e. our first record would have returned 7/4/1996 1:12:00 PM –07:00, which is not the same as what we ended up with.

Forcing another DateTime to a Different Time Zone

Now we need to deal with the ShippedDate column, forcing that to my local time.  I could just select the column and turn it into a Date/Time/Timezone data type, but I won’t.  Why?  What if I send this workbook to another user?  It will return THEIR time zone, not mine.  And that could be different.  Much better to explicitly set it.

  • Add Column –> Add Custom Column
    • Name:  ShippedDate (UTC –7:00)
    • Formula:  DateTime.AddZone([ShippedDate],-7)

Notice that this time we do force it to be in the –7 time zone, as these DateTimes originated from that time zone. The result:

SNAGHTML1f0e9e46

Fantastic.  We’ve added time zone data, without changing the original times.

Let’s just go do a little bit of cleanup now:

  • Select the OrderDate and ShippedDate columns
  • Transform –> Data Type –> Date/Time
  • Select OrderDate (UTC +0:00) through ShippedDate (UTC –7:00)
  • Transform –> Date Type –> Date/Time/Timezone

Excellent.  Now they should show up correctly when we load them to an Excel table instead of losing their formatting.

Making Comparisons

We’re at the final step now: Working out the time to ship.  This is relatively straight forward:

  • Add Column –> Add Custom Column
    • Name:  Days to Ship
    • Formula:  [#"ShippedDate (UTC -7:00)"]-[#"OrderDate (UTC -7:00)"]
  • Select the Days to Ship column
  • Transform –> Data Type –> Duration

Note:  You can just double click the column names in the formula wizard and it will put the # characters in there for you.

And the final look in Power Query:

SNAGHTML1f1602fa

With that all complete, the final step is to give the query a name (I chose ShippingTimes) and load it to a worksheet:

image

Final Thoughts

Personally, I like to take explicit control over my data types.  Call me a control freak if you like (I’ve been called much worse) but relying on implicit conversions that set to “local time” scare me a bit, particularly if I’m going to be sending my workbook off to someone who lives in a different zone than I do.  Once you know how to do this it’s not super difficult, and I now know EXACTLY how it will represent on their side.

I’ll admit also that I’m a bit disappointed in the UI for datetime conversions.  To me, anyone playing in this field needs very granular control over every column.  An extra step in the Transform to Date/Time/Timzone step would go a long way to solving this, as you’d be able to skip writing custom formulas.  Hopefully that’s on the Power Query team’s radar for the future, as well as a full datetime menu that would allow us to easily choose from/add/convert to the majority of the formulas found in the article referenced above.

Power Query Training

Also don't forget.  If you love Power Query or are intrigued by the things you can do with it, we have an online training course coming up soon.  Check it out and register at www.powerquery.training/course

9 thoughts on “Data From Different TimeZones

  1. Thanks for the thorough explanation, Ken.

    I agree there should be the ability to set the time zone in the time transformation tools. Having 'Local Time' in the Add Column/Transform > Time menu is a bit ‘cart before the horse’. We need to be able to set the time zone in the first place, as I doubt many data sources are going to have it already populated.

    Mynda

  2. I think it's even more than that, to be honest. I honestly don't care about the time zone in 99.9% of my use cases, so the ability to add my local time zone easily is actually pretty irrelevant most of the time.

    As soon as I reach for that feature though, the very fact that I'm worrying about it indicates that I'm trying to deal with data that is in different time zones. Defining everything as "local" isn't really helpful if I don't have an easy way to define data as coming from a different zone. I'd give up the ability to set the data to local time in favour of being prompted to supply the time zone in a heartbeat.

  3. Ken-

    Thanks much for this article. Here we are in "Power BI land" now; the same limitation still exists, and your solution still works. 🙂 (BTW, I raised this issue on the ideas.powerbi forum, and linked to this article for reference - readers, add your votes! See the "website" link.)

    One further question I'm wrestling with...daylight savings time. Part of the year I'm at UTC-7 (PST), the rest of the year I'm at UTC-8 (PDT). My requirement is to bucket the time into 3 hour chunks so activity can be analyzed within each bucket....but that bucket is going to slip an hour back and forth at each DST boundary unless I deal with DST in the conversion...I can't just let it sit static at either -8 or -7.

    Have you faced a similar requirement? Any clever solutions? 🙂

    Cheers!
    Brent

  4. A big P.S.

    After posting the above, I stopped being lazy and did a quick look for myself.

    DateTimeZone.ToLocal(DateTime.AddZone([DateTimeExample],0))

    This function yields the appropriate -7 or -8 based on whether the date it is fed is in daylight savings time or not. (I transposed them above: the -8 is really PST and -7 is PDT. More evidence that I should be letting the function do it, not try to roll my own! :-))

    Cheers!
    Brent

  5. Pingback: Solving DAX Time Zone Issue in Power BI | RADACAD

  6. The resulting duration in Excel is a customized date/time format: d.hh:mm:ss invoking 2 issues:
    1. Negative durations result in errors.
    2. The "d" part of the customized format is actually the day part of a date.
    So a duration of 1 day 12 hours is rather Jan 1, 1900 12:00:00; so far so good but a duration of 32 days 12 hours is rather Feb 1, 12:00:00 or 1.12:00:00 instead of 32.12:00:00.
    I would expect durations in Excel be formatted as a number, just like in the datamodel (both in Power Pivot and Power BI).

    Regards,
    Marcel Beugelsdijk, Bemint, The Netherlands

  7. I'm not sure I'm totally following here, Marcel, but if I read this correctly... you can probably force it to be correct by converting the duration to a decimal value before you write it into the Excel table, then format it as a date there. I'm not sure I would trust Excel with a duration type, as it doesn't really know anything about that data type.

  8. Hi Ken,

    You can try and adjust the month of the first Shipped Date in Sheet1 from 7 to 9 and refresh the query results. Days to ship in Excel will read 14.09:38:24 instead of 74.09:38:24. If you select the field you can see in the formula bar a date/time value on March 14, 1900.

    It's obvious indeed you can't trust Excel with a duration. My choice would be either to convert to a number and leave it a number in Excel or to have Power Query convert the duration to text: Duration.ToText([Days to Ship]),

    Otherwise your solution is working fine with me at UTC +2:00!

    Cheers,
    Marcel

  9. Ah, I see what you mean. There's something lost in translation from the duration back to Excel's date serial number. My temptation would probably be to separate the day portion from the time in Power Query or something.

Leave a Reply

Your email address will not be published. Required fields are marked *