Date.FromText is a Power Query M function that creates a date value from a textual representation, with an optional record parameter for additional properties. The function returns a date value based on the input text and formatting options.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Date.FromText( text as nullable text, optional options as any, ) as nullable date
|Text||The text value to convert into a date.|
|Options||Optional||This is an optional argument where you can provide a record parameter with additional instructions. The |
The Date.FromText formula converts a text value into a date. You can use an optional
record parameter to provide additional instructions for the conversion.
Here’s what you can include in that record:
Format: Specifies the format you want for the date. If you leave this field empty or use ‘null,’ the function will do its best to understand the date format for you. Use any of the custom formats provided later in this post.
Culture: This comes into play when you’ve defined a ‘Format’. It adjusts certain format elements based on cultural settings. For instance, in the U.S., “MMM” would mean “Jan,” “Feb,” “Mar,” etc., whereas in Russia, it would translate to “янв,” “фев,” “мар,” and so on. If ‘Format’ is left as ‘null,’ the ‘Culture’ sets the default date format. When you don’t specify ‘Culture,’ the function defaults to the current system setting (using Culture.Current).
To support older workflows, you can also pass in a simple text value for ‘options.’ This behaves as if you set ‘options’ to [Format = null, Culture = options].
Let’s look at some examples of how to extract a date from a text value.
Converting Standard Date Format
Suppose you want to convert the text “2023-12-31” into a date value. You can use the code:
// Output: #date( 2010, 12, 31 ) Date.FromText( "2023-12-31" )
Some notations differ depending on the language your report in. For example, in the Netherlands, the text “31 mrt 2023” is identical to the English “31 Mar 2023“.
The Dutch culture writes months in lower letters and uses a different abbreviation from English. To extract a date from this value, you can specify both a custom format string and a culture code.
// Output: #date( 2024, 3, 31 ) Date.FromText( "31 mrt 2024", [Format="dd MMM yyyy", Culture="nl-NL"] )
Using Culture Codes for Calendars
The culture code serves purposes beyond language; it can also adjust the calendar system in use. As an example, the code below takes a date from the year 1400 in the Hijri calendar and converts it to a Gregorian date:
// Output: #date( 1979, 11, 20 ) Date.FromText( "1400", [Format="yyyy", Culture="ar-SA"] )
Custom Format Strings: A Quick Reference
For your convenience, a list of available custom format strings can be found in the table below.
|%d||Single Digit Day – 0 to 30||31||1|
|dd||Double Digit Day – 00 to 30||31||01|
|ddd||Short Weekday Name||Sun||Sat|
|dddd||Full Weekday Name||Sunday||Saturday|
|%M||Single Digit Month – 1 to 12||12||2|
|MM||Double Digit Month – 01 to 12||12||02|
|MMM||Short Month Name||Dec||Feb|
|MMMM||Full Month Name||December||February|
|%y||Year – 0 to 99||23||3|
|yy||Year – 00 to 99||23||03|
|yyy||Year with at least three digits||2023||2003|
|m, M||Day followed by Full Month Name||31 December||1 February|
|y, Y||Standard Long Date||December 2023||February 2003|
|d||Standard Short Date||31/12/2023||01/02/2003|
|D||Full Long Date||31 December 2023||01 February 2003|
|%g, gg||The period of an era||A.D.||A.D.|
Learn more about Date.FromText in the following articles:
- Extract Date From Text String in Power Query
This post shows how to extract a date from text values in Power Query. You can convert almost any string to a date with this trick! » Read more
- Date Formatting in Power Query – Master 18 Custom Formats
This article shows how you can use Custom Format Strings to format date values. » Read more
- 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 Date.FromText are: