DateTimeZone.From - Function

DateTimeZone.From is a Power Query M function that converts various types of values to a datetimezone value with local timezone information. The function returns a datetimezone value based on the input value and optional culture.

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

Syntax

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

Description

The DateTimeZone.From function returns a datetimezone value derived from the given value. An optional culture parameter can also be provided (for example, “en-US”). If the given value is null, DateTimeZone.From returns null. If the given value is a datetimezone, the value is returned. The function can convert values of the following types into a datetimezone value:

text: A datetimezone value from textual representation. Refer to DateTimeZone.FromText for details.
date: A datetimezone with value as the date component, 12:00: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 of the OLE Automation Date expressed by value and the offset corresponding the local time zone.

If value is of any other type, an error is returned.

Examples

To better understand how DateTimeZone.From works, let’s look at a few examples. This function can convert “2023-10-30T02:30:00-02:00” into a datetimezone value:

// Output: #datetimezone( 2023, 10, 30, 2, 30, 0, -2, 0 ) 
DateTimeZone.From( "2023-10-30T02:30:00-02:00" )

If you omit certain information, the function will do its best to return a value. Not providing a time value will return 00:00:00. If you skip the timezone value, the function will use the local timezone value found on the machine where the refresh happens:

// Output: #datetimezone( 2023, 10, 30, 2, 30, 0, 1, 0 ) 
DateTimeZone.From( "2023-10-30T02:30:00" )
// Output: #datetimezone( 2023, 10, 30, 0, 0, 0, 1, 0 ) 
DateTimeZone.From( "2023-10-30" )

You can also use the AM/PM notation to provide a time value:

// Output: #datetimezone( 2023, 10, 30, 12, 0, 0, 1, 0 ) 
= DateTimeZone.From( "2023-10-30 12PM" )
// Output: #datetimezone(2023, 10, 30, 6, 0, 0, 1, 0 ) 
= DateTimeZone.From( "2023-10-30 6AM" )

When dealing with specific notations, it can be helpful to provide a culture parameter that instructs the function how to interpret the text. For example, extracting the date from a string is interpreted differently in the US compared to the Netherlands:

// Output: #datetimezone( 2023, 10, 5, 6, 0, 0, 2, 0 ) 
DateTimeZone.From( "10-05-2023 6AM", "en-US" )
// Output: #datetimezone( 2023, 5, 10, 6, 0, 0, 2, 0 ) 
DateTimeZone.From( "10-05-2023 6AM", "nl-NL" )

In conclusion, DateTimeZone.From can convert a variety of value types into a datetimezone value. It’s especially useful when dealing with data across different timezones.

Other functions related to DateTimeZone.From are:

Last update: August 25, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/datetimezone-from
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.

Syntax

Description

Examples

Related functions