DateTimeZone.FromText
DateTimeZoneConverts a text representation to a datetimezone value.
Syntax
DateTimeZone.FromText(text as nullable text, optional options as any) as nullable datetimezoneParameters
| Name | Type | Required | Description |
|---|---|---|---|
text | text | Yes | A text string representing a datetimezone, such as "2024-03-15T14:30:00+05:30" or "2024-03-15 14:30:00Z". |
options | any | No | A record or culture text string. Supported record fields: Format (custom format string) and Culture (e.g., "en-US"). |
Return Value
datetimezone — A datetimezone value parsed from the given text, or null if the input is null.
Remarks
DateTimeZone.FromText parses a text value into a datetimezone. Without options, it attempts ISO 8601 formats with timezone offsets such as "2024-03-15T14:30:00+00:00", "2024-03-15T14:30:00-05:00", or "2024-03-15T14:30:00Z". Use the options record to supply a custom Format pattern or Culture for non-standard text representations. Format strings follow .NET custom datetime format conventions, and zzz produces the UTC offset in ±HH:mm notation.
Always specify both Format and Culture when parsing locale-specific strings. Without an explicit Culture, parsing behavior depends on the host machine's locale and may produce inconsistent results between Power BI Desktop and Power BI Service. If the source system emits UTC (indicated by a Z suffix or +00:00 offset), the parsed value will carry a +00:00 offset and can be used directly for UTC comparisons.
If the text is null, the function returns null. If parsing fails, the function raises an error. Use try DateTimeZone.FromText(...) otherwise null to handle malformed rows gracefully without stopping the entire query.
Examples
Example 1: Parse ISO 8601 datetimezone strings
#table(
type table [TextValue = text, Parsed = datetimezone],
{
{"2024-01-15T08:30:00+00:00", DateTimeZone.FromText("2024-01-15T08:30:00+00:00")},
{"2024-03-15T14:30:00-05:00", DateTimeZone.FromText("2024-03-15T14:30:00-05:00")},
{"2024-06-01T09:00:00Z", DateTimeZone.FromText("2024-06-01T09:00:00Z")}
}
)TextValue | Parsed | |
|---|---|---|
| 1 | 2024-01-15T08:30:00+00:00 | 1/15/2024 8:30:00 AM |
| 2 | 2024-03-15T14:30:00-05:00 | 3/15/2024 7:30:00 PM |
| 3 | 2024-06-01T09:00:00Z | 6/1/2024 9:00:00 AM |
Example 2: Parse a specific datetimezone format
#table(
type table [TextValue = text, Parsed = datetimezone],
{{"2024-03-15 14:30:00 +0530",
DateTimeZone.FromText("2024-03-15 14:30:00 +0530", [Format="yyyy-MM-dd HH:mm:ss zzz", Culture="en-US"])}}
)TextValue | Parsed | |
|---|---|---|
| 1 | 2024-03-15 14:30:00 +0530 | 3/15/2024 9:00:00 AM |
Example 3: Safe parsing with try...otherwise for mixed data
#table(
type table [TextValue = text, Parsed = datetimezone],
{
{"2024-01-15T08:30:00+00:00", try DateTimeZone.FromText("2024-01-15T08:30:00+00:00") otherwise null},
{"not a date", try DateTimeZone.FromText("not a date") otherwise null},
{"2024-03-15T14:30:00Z", try DateTimeZone.FromText("2024-03-15T14:30:00Z") otherwise null}
}
)TextValue | Parsed | |
|---|---|---|
| 1 | 2024-01-15T08:30:00+00:00 | 1/15/2024 8:30:00 AM |
| 2 | not a date | null |
| 3 | 2024-03-15T14:30:00Z | 3/15/2024 2:30:00 PM |