Date.ToText

Updated on

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
ArgumentAttributeDescription
DateThe date value to transform into text.
OptionsOptionalThis 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.
CultureOptionalThis 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.

FormatDescriptionDecember 31, 2023February 1, 2003
%dSingle Digit Day – 0 to 30311
ddDouble Digit Day – 00 to 303101
dddShort Weekday NameSunSat
ddddFull Weekday NameSundaySaturday
%MSingle Digit Month – 1 to 12122
MMDouble Digit Month – 01 to 121202
MMMShort Month NameDecFeb
MMMMFull Month NameDecemberFebruary
%yYear – 0 to 99233
yyYear – 00 to 992303
yyyYear with at least three digits20232003
yyyyFour-Digit Year20232003
yyyyyFive-Digit Year0202302003
m, MDay followed by Full Month NameDecember 31February 1
y, YStandard Long DateDecember 2023February 2003
dStandard Short Date12/31/20232/1/2003
DFull Long DateSunday, December 31, 2023Saturday, February 1, 2003
%g, ggThe period of an eraA.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.

Date.ToText with Custom date Formats in Power Query M

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?

Date.ToText with Custom date Format and Culture Code in Power Query M

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:

Other functions related to Date.ToText are:

BI Gorilla Blog

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/date-totext