DateTimeZone.FromText

Updated on

DateTimeZone.FromText is a Power Query M function that generates a datetimezone value from a textual representation with optional format and culture settings. The function returns a datetimezone value based on the input text and options.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

DateTimeZone.FromText(
   text as nullable text,
   optional options as any,
) as nullable datetimezone

Description

Creates a datetimezone value from a textual representation, text. An optional record parameter, options, may be provided to specify additional properties. The record can contain the following fields:

  • Format: A text value indicating the format to use. Find more details on Custom date and time format strings or go here for Standard TimeSpan format strings. Omitting this field or providing null will result in parsing the date using a best effort.
  • Culture: When Format is not null, Culture controls some format specifiers. For example, in "en-US" "MMM" is "Jan", "Feb", "Mar", ..., while in "ru-RU" "MMM" is "янв", "фев", "мар", .... When Format is nullCulture controls the default format to use. When Culture is null or omitted, Culture.Current is used.

To support legacy workflows, options may also be a text value. This has the same behavior as if options = [Format = null, Culture = options].

Examples

To better understand how DateTimeZone.FromText works, let’s look at a few examples. This function can convert “2010-12-31T01:30:00-08:00” into a datetimezone value:

// Output: #datetimezone( 2010, 12, 31, 1, 30, 0, -8, 0 )
DateTimeZone.FromText( "2010-12-31T01:30:00-08:00" )

You can also use a custom format and the German culture:

// Output: #datetimezone( 2010, 12, 30, 2, 4, 50.36973, 2, 0 )
DateTimeZone.FromText( 
  "30 Dez 2010 02:04:50.369730 +02:00", 
  [Format="dd MMM yyyy HH:mm:ss.ffffff zzz", Culture="de-DE"] 
)

Another example uses ISO 8601:

// Output: #datetimezone( 2009, 6, 15, 13, 45, 30, -7, 0 )
DateTimeZone.FromText( 
  "2009-06-15T13:45:30.0000000-07:00", 
  [Format="O", Culture="en-US"] 
)

In conclusion, DateTimeZone.FromText can convert a text representation into a datetimezone value. It’s especially useful when dealing with data across different timezones.

Other functions related to DateTimeZone.FromText are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/datetimezone-fromtext