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.
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.
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
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
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):
This returns a datetimezone value from a text value. For more details, see DateTimeZone.FromText (below)
A datetimezone with value as the date component, 12:0:00 AM as the time component and the offset corresponding the local time zone
A datetimezone with value as the datetime and the offset corresponding the local time zone
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
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.
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?
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.
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.
This returns a datetime value with the zone information removed from the input datetimezone value.
DateTimeZone.RemoveZone(datetimezone as datetimezone) as nullable datetime
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:
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.
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.
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”).
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:
This returns a datetimezone value set to the current system date and time in the UTC timezone.
DateTimeZone.UtcNow as datetimezone
This function must receive no parameters; it gives the current datetimezone with reference to UTC.
This returns a time zone hour value from a datetimezone value.
DateTimeZone.ZoneHours(datetimezone as datetimezone) as nullable number
This returns a Timezone minute value from a datetimezone value.
DateTimeZone.ZoneMinutes(datetimezone as datetimezone) as nullable number.
Come back next time for more ways to use Power Query!