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
Syntax
Date.ToText(
date as nullable date,
optional options as any,
optional culture as nullable text,
) as nullable text| Argument | Attribute | Description |
|---|---|---|
| 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 Format field is used to specify the output format of the transformation. You can also provide a Culture field to ensure the transformation supports local formats. For example: [ Format = “d MMM yyyy”, Culture = “nl-NL” ]. If you omit this argument, the default format used will align with the culture of your system, using Culture.Current. |
| Culture | Optional | This is an optional argument used for legacy workflows only. You can specify the culture as a text value here. |
Description
Date.ToText converts a date value into its textual representation. Optional parameters (options) in a record format can be provided to define the format and cultural settings for the conversion.
Examples
Let’s look at some practical examples of how to use the Date.ToText function:
Basic conversion
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 |
| yyyy | Four-Digit Year | 2023 | 2003 |
| yyyyy | Five-Digit Year | 02023 | 02003 |
| 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.
Culture Code
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.
Related articles
Learn more about Date.ToText in the following articles:
- 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 - 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
Related functions
Other functions related to Date.ToText are:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy