Date.ToText is a Power Query M function that returns a textual representation of date with optional formatting and culture parameters.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Date.ToText( date as nullable date, optional options as any, optional culture as nullable text, ) as nullable text
|Date||The date 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.|
The Date.ToText function returns a textual representation of a date. An optional record parameter, options, can be provided to specify additional properties. The ‘culture’ argument is primarily used for legacy workflows.
Let’s look at some practical examples of how to use the Date.ToText function:
You can convert #date(2023, 12, 31) into a text value. The resulting output may vary depending on the current culture.
Date.ToText( #date( 2023, 12, 31 ) ) // My machine's output: "31-12-2023"
Custom Date Formats
Power Query supports a wide range of Custom Date formats. By using the optional ‘options’ argument, you can provide your desired format. For example:
Date.ToText( #date( 2023, 12, 31 ), [Format="dd MMM yyyy"] ) // Output: "31 dec 2023"
The formats relevant to dates are specified in the below picture. For each value in the last 3 columns the Date.ToText function is applied using the Format string of the Format column applied on the value in the column name.
You can find the custom format strings relevant for dates in the table below. For each value in the final two columns, the Date.ToText function is used. The function applies the format string from the ‘Format‘ column to the corresponding value indicated in the column header.
|Format||Description||December 31, 2023||February 1, 2003|
|%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||December 31||February 1|
|y, Y||Standard Long Date||December 2023||February 2003|
|d||Standard Short Date||12/31/2023||2/1/2003|
|D||Full Long Date||Sunday, December 31, 2023||Saturday, February 1, 2003|
|%g, gg||The period of an era||A.D.||A.D.|
By knowing these formatting strings you can now make your own custom date formats. For example, you can return a string that contains a day name and a descriptive date as follows:
Date.ToText( #date( 2023, 12, 31 ), [Format="ddd MMMM d, yyyy"] ) // Output: "Sun December 31, 2023"
The below image shows several variations of what you can achieve by using the format strings from the Format column.
The Date.ToText function also supports a culture code in the ‘options’ argument. You can specify a culture code as follows:
Date.ToText( #date( 2023, 12, 31 ), [Format="MMMM", Culture="nl-NL"] ) // Output: "december"
The culture code can significantly impact the outcome of the Date.ToText function. For example, month names and date formats can vary greatly between countries. The differences between the Dutch (nl-NL) and American (en-US) culture codes provide a clear example of this.
For instance, notice the difference in date format (mm/dd/yyyy vs dd-mm-yyyy) in the picture below? And how the day and month names are spelt in the local language?
In conclusion, the Date.ToText function in Power Query M is a versatile function that can be tailored to meet your specific needs. By understanding its syntax and functionality, you can manipulate date values with ease and get your desired output.
Learn more about Date.ToText in the following articles:
- 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
- Create Date Table with Dynamic Language in Power Query
Want your Date Table in Multiple Languages? This post explains how to make your calendar dynamic to support any language or culture. » Read more
- Create Date Table or Calendar in Power Query M
Learn how to create a dynamic calendar table in Power Query’s M language. Build your custom columns and claim your free Date Table Script. » Read more
Other functions related to Date.ToText are: