How to Convert to Dates with Date.FromText in Power Query M Language

Image by Editor
 

When working with data in Power BI, Excel, or any tool that uses Power Query, you may encounter columns containing dates stored as text. Power Query’s M language provides a powerful function called Date.FromText() that converts text strings into date values.

In this article, we will show how to convert to dates with Date.FromText in Power Query M language.

What is Date.FromText?

The function Date.FromText is a part of the Power Query M Language. It converts a text value into a date value, provided the text follows a recognizable date format.

Syntax:

The basic syntax for the Date.FromText() function is:

Date.FromText(text as text, optional culture as nullable text) as date

Where:

• text: The text string you want to convert to a date.
• culture [Optional]: A text value specifying the culture/locale to use for parsing the date.

Simple Date Conversion Examples

If you have a simple date string such as “2025-04-01”, you can directly convert it using:

Date.FromText("2025-04-01")

This will return a valid date value:

#date(2025, 4, 1)

This works because the text string follows the ISO 8601 format (year-month-day), which is universally accepted in Power Query.

Let’s start with some basic examples:

// Standard date format (yyyy-MM-dd)
Date.FromText("2023-04-15")             // Result: #date(2023, 4, 15)

// US date format (MM/dd/yyyy)
Date.FromText("04/15/2023", "en-US")  // Result: #date(2023, 4, 15)

// UK date format (dd/MM/yyyy)
Date.FromText("15/04/2023", "en-GB")  // Result: #date(2023, 4, 15)

Work with Different Date Formats

Power Query is capable of understanding several commonly used date formats.

Power Query can handle various date formats:

// Month name format
Date.FromText("15 April 2023")          // Result: #date(2023, 4, 15)

// Abbreviated month
Date.FromText("15 Apr 2023")            // Result: #date(2023, 4, 15)

// Date with time (time part is ignored)
Date.FromText("2023-04-15T14:30:00")    // Result: #date(2023, 4, 15)

However, you should be cautious with regional settings. For example, the format “04/01/2025” may be interpreted as either April 1 or January 4 depending on your locale. In the United States (en-US), this is April 1, whereas in the United Kingdom (en-GB), it may be interpreted as January 4.

Use Date.FromText in Power Query Editor

Here’s how to apply this in your data transformations:

• Select your column containing the date text.
• Go to the Add Column tab >> select Custom Column.
• In the Custom Column dialog box;
• Enter the following formula.

= Date.FromText([DateText], "en-US")

• Click OK.

Invalid Date Formats:

The function will return an error if the input text does not represent a complete or valid date. For instance:

Date.FromText("April-2025")

• This will produce an error because the day is missing, and the format is ambiguous.
• To avoid this, always ensure that the input text includes the day, month, and year in a recognizable order.

Handle Culture-Specific Formats

The culture parameter is essential when working with dates formatted according to specific regional standards:

// German format (dd.MM.yyyy)
Date.FromText("15.04.2023", "de-DE")     // Result: #date(2023, 4, 15)
 
// French format
Date.FromText("15 avril 2023", "fr-FR")  // Result: #date(2023, 4, 15)

// Japanese format
Date.FromText("2023年4月15日", "ja-JP")   // Result: #date(2023, 4, 15)

Convert a Column of Text Dates in a Table

Suppose you have a table with a column named “DateText” that contains dates in text format. To convert the entire “DateText” column to actual date values,

• You can use the Table.TransformColumns function as shown below:

= Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"DateText", type date}})

• This will replace the “DateText” column with properly typed date values.

Error Handling of Date Format

Invalid date strings will cause errors. If your dates don’t convert properly, try this approach:

// Error handling with try/otherwise
try Date.FromText("Invalid Date") otherwise null  // Result: null

// Handling multiple possible formats
try Date.FromText([DateText]) otherwise
try Date.FromText([DateText], "en-US") otherwise
try Date.FromText([DateText], "en-GB") otherwise
null

• This will attempt different formats and return null if none work.

Advanced Example: Converting a Table Column

In real-world datasets, date values often appear in different formats depending on the source or region. When working with such tables, it’s important to apply date conversion across an entire column while handling multiple formats and potential errors gracefully.

Here’s a complete example showing how to convert a column of date text values.

let

Source = Table.FromRecords({
[ID = 1, DateText = "2023-04-15"],
[ID = 2, DateText = "04/15/2023"],
[ID = 3, DateText = "15/04/2023"]
}),

ConvertedDates = Table.AddColumn(
Source,
"DateValue",
each try Date.FromText([DateText], "en-US") otherwise null
)

in
ConvertedDates

In this example, a new column named ConvertedDate is created by attempting to convert each text date using the en-US locale. If a text value does not match the expected format, the expression safely returns null instead of breaking the query.

Practical Implementation in Power Query

Here’s how to apply this in your Power Query Editor:

• Select the column containing the date text.
• Right-click and select Change Type.
• Choose the Using Locale… option.

• Select Date as the type and the appropriate locale.
• Click OK.

Or add a custom column with the formula:

= Date.FromText([YourDateTextColumn], "en-US")

Common Issues and Solutions

• Ambiguous dates: For formats like “01/02/2023” (is it January 2nd or February 1st?), always specify the culture parameter.
• Incomplete dates: If your dates lack year information, you may need to add it first:
• = Date.FromText(“April 2023″ & ” 1″)  // For a month-year combination
• Inconsistent formats: Use pattern matching or conditional logic to standardize date formats before conversion.

Conclusion

The Date.FromText function is essential when working with imported or user-generated data that includes dates stored as text. With proper handling and error management, you can clean and transform your data for any reporting. By mastering Date.FromText(), you can efficiently transform text-based dates into proper date values in Power Query, enabling date-based calculations, filtering, and visualizations in your Power BI reports and Excel data models.