Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Formatting Dates in Power Query M

Date formatting is a frequent task in Power Query’s M language. While M provides its own format for date values, you may sometimes need to present dates differently. A common but inefficient method is to manually extract date parts and combine them back together.

This article illustrates a more effective approach: the Date.ToText function, which allows for customizable date formatting.

To illustrate, consider a date you want to work with, such as January 31, 2024. You can define this date in M language as follows:

myDate = #date( 2024, 1, 31 )

Now, let’s delve into some examples.

The Naive Approach to Date Formatting

Suppose you’re tasked with formatting a date as “2024-01-31.” One way to achieve this is by breaking down the date into its individual components—year, month, and day—and then reassembling them as text:

// Output: "2024-01-31"
Text.From ( Date.Year ( MyDate ) )
  & "-"
  & Text.PadStart ( Text.From ( Date.Month ( MyDate ) ), 2, "0" )
  & "-"
  & Text.From ( Date.Day ( MyDate ) )

In this method, each date component is converted to text. For months with single digits, leading zeros are added manually.

In another scenario, you need to create some code to return a text value that says: “January 31, 2024 will be on Wednesday”. Here’s one way to go about it:

// Output: "January 31, 2024 will be on Wednesday"
Date.MonthName ( MyDate )
  & " "
  & Text.From ( Date.Day ( MyDate ) )
  & ", "
  & Text.From ( Date.Year ( MyDate ) )
  & " will be on "
  & Date.DayOfWeekName ( MyDate )

The Downsides of the Naive Approach

While these methods work for date formatting, they come with disadvantages:

  • Prone to Errors: The more expressions you write, the higher the risk of making mistakes.
  • Verbose Code: The code becomes long and hard to read.
  • Inflexibility: Modifying your output requires you to search through the code to find what needs changing.

Better Date Formatting: Using Date.ToText

The Date.ToText function offers a cleaner, more efficient way to format date values in Power Query. It accepts a date value and an optional set of parameters for formatting and culture settings.

Syntax of Date.ToText Function

The function’s syntax is quite straightforward:

Date.ToText(
   date as nullable date,
   optional options as any
)

The first argument is the date you want to format, and the second argument lets you specify options like a Format or a Culture code. Here’s how that works. Let’s explore how this works with examples.

Formatting with Custom Strings

To output a string like “2024-01-31”, you can provide the following custom formatting string:

// Output: "2024-01-31"
Date.ToText( #date( 2024, 1, 31 ), [ Format = "yyyy-MM-dd" ] )

The above example is shorter, much more readable and easy to maintain. The Custom Format String instructs the following:

  • yyyy: Outputs the year as “2024.”
  • dd: Adds the day number, padding single digits with a leading zero.
  • MM: Adds the month number, also padding single digits with a leading zero.

The second example presented above returned “January 31, 2024 will be on Wednesday”. This expression can be replicated with the following code.

// Output: "January 31, 2024 will be on Wednesday"
Date.ToText( #date( 2024, 1, 31 ), [ Format = "MMMM %d, yyyy' will be on' dddd" ] )

In this example, the custom format string accomplishes several tasks:

  • MMMM: Returns the full month name, “January.”
  • %d: Outputs the day number without leading zeros, “31.
  • ‘ will be on’: Uses single quotes to include literal text in the output.
  • yyyy: Outputs the year “2024.”
  • dddd: Outputs the full day-of-week name, “Wednesday.”

Using Date.ToText reduces the length of your code and likely improves performance. Additionally, it adds readability and maintainability. So, how can you learn what custom format strings are available?

Custom Format String Options

The M language offers a variety of custom format strings for date formatting. These strings enable highly specific date outputs.

Below is a table that outlines these format strings. They can be used in the options argument as Format for the Date.ToText function.

The table’s header contains two date values that were used with the format string. When you find format strings separated by commas, it means that both values give identical results.

FormatDescriptionDecember 31, 2023February 1, 2003
%dSingle Digit Day (1-31)311
ddDouble Digit Day (01-31)3101
dddShort Weekday NameSunSat
ddddFull Weekday NameSundaySaturday
%MSingle Digit Month (1-12)122
MMDouble Digit Month (01-12)1202
MMMShort Month NameDecFeb
MMMMFull Month NameDecemberFebruary
%yYear (0-99)233
yyYear (00-99)2303
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.

Using Custom Text Strings

While the above strings are useful for most scenarios, there might be instances where you want to include arbitrary text. This is possible if you enclose the custom text within single quotes.

For instance, the following expression uses the custom text ‘My Wedding will be on’.

// Output: "My wedding will be on January 31, 2024"
Date.ToText( #date( 2024, 1, 31 ), [ Format = "'My wedding will be on 'MMMM dd, yyy" ] )

Or if you’re documenting historical events, including the era may be important:

// Output: "Boxer Rebellion - 1900 A.D."
Date.ToText(   #date( 1900, 5, 31 ), [ Format = "'Boxer Rebellion - 'yyyy gg"] )

See how these custom format strings make your date formatting much easier? At the time of writing, a bug exists concerning the escape character’s support for certain characters.

For example, including the letter “s” within single quotes can result in the error: “Parameter.Error: We couldn’t convert the text value to date using the specified format. The format includes a time component”.

This issue has been reported to Microsoft and will hopefully be resolved in future updates.

Conclusion

Mastering custom format strings in Power Query can significantly simplify your date formatting tasks. By combining the Date.ToText function with custom format strings, you can achieve precise and varied date formats in a single line of code, reducing errors and improving code readability.

It offers a one-stop solution that eliminates the need for lengthy code blocks or complicated workarounds. Other values like time, datetime and datetimezone also support custom format strings.

To master formatting strings, have a look at this guide that shows all other format strings available in the M language.

Hope that was useful and happy querying!

Share on:

Latest from my blog

  1. Hi Rick,

    How would you chronologically sort a dd/MM formatted column created using the Date.ToText method?

    Thanks in advance 🙂

    Reply
    • Whenever you need an output string with a combination of text (based on the date) and a string. Often they are intertwined and the ‘arbitrary text’ then helps in getting the desired output.

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.