DateTimeZone.From

Updated on
Rick de Groot

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
ArgumentAttributeDescription
valueThe value to transform into a datetimezone value.
cultureoptionalThe culture argument enables the specification of a Culture code (e.g., “nl-NL” or “en-US”) to align transformations with local formatting conventions. If this argument is omitted, functions default to Culture.Current, which reflects the system’s regional settings.

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:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy