DateTime.FromText
DateTimeConverts a text representation of a datetime to a datetime value.
Syntax
DateTime.FromText(text as nullable text, optional options as any) as nullable datetimeParameters
| Name | Type | Required | Description |
|---|---|---|---|
text | text | Yes | A text string representing a datetime, such as "2024-03-15 14:30:00" or "March 15, 2024 2:30 PM". |
options | any | No | A record or culture text string. Supported record fields: Format (custom format string) and Culture (e.g., "en-US"). |
Return Value
datetime — A datetime value parsed from the given text, or null if the input is null.
Remarks
DateTime.FromText parses a text value into a datetime. Without options, it attempts common ISO 8601 datetime formats such as "2024-03-15T14:30:00" or "2024-03-15 14:30:00". Use the options record to supply a Format pattern or a Culture for locale-specific strings such as "03/15/2024 02:30:00 PM". Format strings follow .NET custom datetime format conventions.
Always specify both Format and Culture when parsing locale-specific strings. Without Culture, parsing behavior depends on the host machine's locale, which can cause different results in Power BI Desktop vs. Power BI Service. A culture of "en-US" is recommended for most M development since it produces predictable results across environments.
If the text is null, the function returns null. If parsing fails, the function raises an error. Use try DateTime.FromText(...) otherwise null to handle mixed or malformed data gracefully without crashing the query.
Examples
Example 1: Parse ISO datetime text strings
#table(
type table [TextValue = text, Parsed = datetime],
{
{"2024-01-15T09:30:00", DateTime.FromText("2024-01-15T09:30:00")},
{"2024-02-01T16:20:00", DateTime.FromText("2024-02-01T16:20:00")},
{"2024-03-05T08:00:00", DateTime.FromText("2024-03-05T08:00:00")}
}
)TextValue | Parsed | |
|---|---|---|
| 1 | 2024-01-15T09:30:00 | 1/15/2024 9:30:00 AM |
| 2 | 2024-02-01T16:20:00 | 2/1/2024 4:20:00 PM |
| 3 | 2024-03-05T08:00:00 | 3/5/2024 8:00:00 AM |
Example 2: Parse a locale-specific datetime string
#table(
type table [TextValue = text, Parsed = datetime],
{{"03/15/2024 02:30:00 PM", DateTime.FromText("03/15/2024 02:30:00 PM", [Format="MM/dd/yyyy hh:mm:ss tt", Culture="en-US"])}}
)TextValue | Parsed | |
|---|---|---|
| 1 | 03/15/2024 02:30:00 PM | 3/15/2024 2:30:00 PM |
Example 3: Safe parsing with try...otherwise for mixed data
#table(
type table [TextValue = text, Parsed = datetime],
{
{"2024-01-15T09:30:00", try DateTime.FromText("2024-01-15T09:30:00") otherwise null},
{"N/A", try DateTime.FromText("N/A") otherwise null},
{"2024-03-12T11:00:00", try DateTime.FromText("2024-03-12T11:00:00") otherwise null}
}
)TextValue | Parsed | |
|---|---|---|
| 1 | 2024-01-15T09:30:00 | 1/15/2024 9:30:00 AM |
| 2 | N/A | null |
| 3 | 2024-03-12T11:00:00 | 3/12/2024 11:00:00 AM |