DateTimeZone.Type

Updated on

DateTimeZone.Type is a type facet in the Power Query M language. It represents the default type claim for the ‘type datetimezone’. It is used primarily for interactions with external systems where date and time values along with their time zone information are critical. The base type of ‘type datetimezone’ in Power Query handles date and time values with timezone context,

Examples

The most effective way to grasp the use of the DateTimeZone.Type is by examining some real-world examples. Here are a few scenarios where the type is used..

When you add a new column in Power Query, designating the data type for the column is an important practice. This not only maintains the uniformity of your data but also mitigates the potential for errors due to data type discrepancies.

In the following instance, we add a new column named “Value” to a table, designated as “Source”. We assign a data type to our value by providing DateTimeZone.Type as the 4th optional argument of Table.AddColumn.

Table.AddColumn(
   Source, 
   "Value", 
   each DateTimeZone.FromText( "2023-06-07T12:30:45+02:00" ), 
   DateTimeZone.Type 
)

In this scenario, we employ the Table.AddColumn function to introduce a new column to the Source table. The DateTimeZone.FromText function converts the string “2023-06-07T12:30:45+02:00” into a DateTimeZone value, and DateTimeZone.Type ensures that Power Query treats the values in this column as DateTimeZone values.

DateTimeZone.Type also proves valuable when creating tables from scratch in Power Query. Using the #table function, you can prescribe a column’s data type before supplying the column values. Consider the following example where we specify a column in a table to be of DateTimeZone.Type:

#table( 
   type table [ Value = DateTimeZone.Type ], 
   { { DateTimeZone.FromText("2023-06-07T12:30:45+02:00") } } 
)

Here, the #table function creates a new table. The type table statement outlines the table structure, indicating it will include a column named “Value” of DateTimeZone.Type. We then provide a DateTimeZone value for the column, using DateTimeZone.FromText to convert a string to a DateTimeZone value.

Related facets

Other related facets are:

Contribute » | Contributors: Rick de Groot