DateTimeZone.ToText is a Power Query M function that converts a datetimezone value to a textual representation based on the provided format and culture options. The function returns a text value representing the datetimezone in the specified format.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
DateTimeZone.ToText( dateTimeZone as nullable datetimezone, optional options as any, optional culture as nullable text, ) as nullable text
|Time||The DateTimeZone value to transform into text.|
|Options||Optional||This is an optional argument where you can provide a record parameter with additional instructions. The |
|Culture||Optional||This is an optional argument used for legacy workflows only. You can specify the culture as a text value here.|
DateTimeZone.ToText function transforms a DateTimeZone value into a readable text form. You can influence how the function performs by giving it specific options in a record format. Two key options you can set are
Culture. Each has a considerable effect on the function’s output.
Fields in the Record:
- Format: This is a text field that sets the time display format. A full list of supported formats for time values is available later in this article. If you don’t provide this field, or if you set it to null, the function will use a default format, which is determined by the
- Culture: This option comes into play in two scenarios. First, when you have provided a
Culturesetting modifies certain aspects of that format. For example, the afternoon time in the United States (“en-US”) will display “PM,” whereas in the Netherlands (“nl-NL”) it won’t display anything. Second, if
Formatis set to null,
Culturesets the default time format. If
Cultureis also set to null or left blank, the system’s current culture setting will be used.
To support legacy workflows,
culture may also be text values. This has the same behavior as if
options = [Format = options, Culture = culture].
To better understand how DateTimeZone.ToText works, let’s look at a few examples.
This function can convert the datetimezone value #datetimezone( 2010, 12, 31, 01, 30, 25, 2, 0 ) into a text value.
// Output: "12/31/2023 1:30:25 AM +02:00" DateTimeZone.ToText( #datetimezone( 2023, 12, 31, 01, 30, 25, 2, 0 ) )
This is particularly useful when you need to display the date and time in a human-readable format, such as in a report or a user interface. However, when the output of this operation does not satisfy your needs you should look for alternatives.
And instead of extracting parts of the DateTimeZone value and combining them back together, why not use Custom Format Strings?
Custom Format Strings
Custom Format Strings instruct the DateTimeZone.ToText function to returns specific items from a DateTimeZone value.
For instance, to return the current time in GMT format you can make use of the RFC1123 pattern:
// Output: "Sat, 30 Dec 2023 23:30:25 GMT" DateTimeZone.ToText( #datetimezone( 2023, 12, 31, 01, 30, 25, 2, 0 ), "r" )
This operation moved the time back two hours to get in line with the ‘GMT’. It also added the GMT designator at the end of the string. In another case you may want to return the text ‘UTC’ instead.
Since the format strings don’t support this output, you will have to manually create it.
// Output: "2023-12-31 01:30:25 UTC" DateTimeZone.ToText( #datetimezone( 2023, 12, 31, 01, 30, 25, 2, 0 ), "yyyy-MM-dd HH:mm:ss 'UTC'" )
In this example the single quotes function as escape character, so you can provide manual text at the end of the string.
Supported Format Strings
Customizing the output of DateTimeZone.ToText involves understanding the available custom format strings. Here’s a table that contains the format strings that are supported for time values:
|Format||Description||12/31/2023 1:30:25 -02:00||12/31/2023 1:30:25 +10:00|
|%z||UTC offset in hours||-2||10|
|zz||UTC offset in hours with leading zero||-02||10|
|%K, zzz||UTC offset in hours and minutes||-02:00||+10:00|
|r, R||RFC1123 pattern||Sun, 31 Dec 2023 03:30:25 GMT||Sat, 30 Dec 2023 15:30:25 GMT|
|o, O||Round-trip date/time pattern||2023-12-31T01:30:25.0000000-02:00||2023-12-31T01:30:25.0000000+10:00|
Note that the above table contains the format strings that are only relevant to DateTimeZone values. Since a DateTimeZone value contains a date value and a time value you can apply any custom format string that is relevant to those value types as well. You can find them in the Date.ToText, the Time.ToText and the DateTime.ToText article.
To learn the possibilities and see what other format strings are there, you can have a look at this Overview of all Custom Format Strings.
Next to custom formatting strings, the culture on your machine can also make an impact on the function output. For instance, let’s say you want to return a custom format for the German culture:
// Output: "30 Dez 2023 02:04:50.369730 -08:00" DateTimeZone.ToText( #datetimezone( 2023, 12, 30, 2, 4, 50.36973, -8,0 ), [Format="dd MMM yyyy HH:mm:ss.ffffff zzz", Culture="de-DE"] )
The start of the output shows “30 Dez”, which is the german short month format. Being able to return such an output is useful when you need to display the date and time in a specific format that is not the default format in your system or the system of others.
Another example uses the ISO 8601 pattern:
// Output: "2023-02-08T03:45:12.0000000+02:00" DateTimeZone.ToText( #datetimezone( 2023, 2, 8, 3, 45, 12, 2, 0 ), [Format="O", Culture="en-US"] )
ISO 8601 is an international standard covering the exchange of date and time-related data. It is useful when you need to display the date and time in a format that is universally recognized and understood.
In conclusion, DateTimeZone.ToText allows you to convert a datetimezone value into a custom string. It’s especially useful when dealing with data across different timezones.
Learn more about DateTimeZone.ToText in the following articles:
- Mastering Custom Format Strings in Power Query
This guide shows how to use custom format strings to format values in the M language. » Read more
Other functions related to DateTimeZone.ToText are: