Power Query: Zoned Out

13 February 2019

Welcome to our Power Query blog. Last week’s blog looked at solving a particular problem by using DateTime.AddZone(). This week, I look at some of the M functionality for dealing with time zones.

There are a number of DateTimeZone() M functions which assist me when dealing with date / time data that has come from a variety of sources. I will look in detail at some of them.

#datetimezone

I use this function to demonstrate many of the other DateTimeZone() functions that follow, since it constructs a datetimezone from parameters passed to it.

#datetimezone(year as number, month as number, day as number, hour as number, minute as number, second as number, offsetHours as number, offsetMinutes as number) as any

The offsetHours and offsetMinutes parameters are the timezone hour and minute values respectively.

DateTimeZone.FixedLocalNow

This returns a datetimezone value set to the current date, time and timezone offset on the system. This value is fixed and will not change with successive calls, unlike DateTime.LocalNow, which may return different values over the course of execution of an expression.

DateTimeZone.FixedLocalNow() as datetimezone

DateTimeZone.FixedUtcNow

This returns the current date and time in “UTC” (Coordinated Universal Time, the GMT timezone). This value is fixed and will not change with successive calls.

DateTimeZone.FixedUtcNow() as datetimezone

DateTimeZone.From

This returns a datetimezone value from a value.

DateTimeZone.From(value as any, optional culture as nullable text) as nullable datetimezone

The value parameter is required, and although it says datatype can be ‘any’, only the following datatypes are accepted (others will result in an error):

text
This returns a datetimezone value from a text value. For more details, see DateTimeZone.FromText (below)

date
A datetimezone with value as the date component, 12:0:00 AM as the time component and the offset corresponding the local time zone

datetime
A datetimezone with value as the datetime and the offset corresponding the local time zone

time
A datetimezone with the date equivalent of the OLE Automation Date of 0 as the date component, value as the time component and the offset corresponding the local time zone

number
A datetimezone with the datetime equivalent the OLE Automation Date expressed by value and the offset corresponding the local time zone

(datetimezone is also accepted, but it will return exactly the same value!).

It is also possible to specify an optional culture parameter. This is defined by Microsoft as

“…a text value corresponding to the culture values supported on your version of Windows, such as ‘en-US’…". If the culture is not specified, the current user culture is used.

DateTimeZone.FromFileTime

This returns a datetimezone from a number value.

DateTimeZone.FromFileTime(filetime as nullable number) as nullable datetimezone

This function is more specialist – filetime has a specific definition:

“A Windows file time value that represents the number of 100-nanoseconds intervals that have elapsed since 12:00 midnight, January 1, 1601 A.D. (C.E.) Coordinated Universal Time (UTC)”. How did you ever live without knowing that?

DateTimeZone.FromText

This returns a datetimezone value from a set of date formats and culture value.

DateTimeZone.FromText(datetimezone as nullable text, optional culture as nullable text) as nullable datetimezone

A full list of acceptable datetimezone formats can be found in Microsoft’s help pages. The culture was previously defined for DateTimeZoneFrom.

DateTimeZone.LocalNow

This returns a datetimezone value set to the current system date and time.

DateTimeZone.LocalNow() as datetimezone

This will provide the current local date time and zone.

DateTimeZone.RemoveZone

This returns a datetime value with the zone information removed from the input datetimezone value.

DateTimeZone.RemoveZone(datetimezone as datetimezone) as nullable datetime

DateTimeZone.SwitchZone

Changes the timezone information for the input datetimezone.

DateTimeZone(datetimezone as datetimezone, timezonehours as number, optional timezoneminutes as nullablenumber) as nullable datetimezone

This adjusts the timezone information on the input datetimezone to timezonehours and timezoneminutes, where provided. This will also affect the time (and possibly the date), as shown in the example screens that follow:

DateTimeZone.ToLocal

This returns a datetimezone value from the local time zone.

DateTimeZone.ToLocal(datetimezone as datetimezone) as nullable datetimezone

This function also adjusts the time (and possibly the date) as the following examples show.

DateTimeZone.ToRecord

This returns a record containing parts of a datetimezone value.

DateTimeZone.ToRecord(datetimezone as datetimezone) as record

This converts a datetimezone to a record – if any parts are missing from datetimezone, then they will be missing from the record.

DateTimeZone.ToText

This returns a text value from a datetimezone value.

DateTimeZone.ToText(datetimezone as nullable datetimezone, optional format as nullable text, optional culture as nullable text) as nullable text

The text output can be controlled by specifying a format (e.g. "yyyy/MM/ddThh:mm:sszzz") and / or a culture (e.g. “US”).

DateTimeZone.ToUtc

This returns a datetimezone value to the UTC time zone.

DateTimeZone.ToUtc(datetimezone as datetimezone) as nullable datetimezone

As mentioned earlier, the Coordinated Universal Time is defined as ‘the primary time standard by which the world regulates clocks and time’. This will affect the time (and possibly the date) as shown in the following example: