Date Formatting in Power Query – Master 18 Custom Formats

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 you to use custom format strings to format your dates.

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. 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.

Format	Description	December 31, 2023	February 1, 2003
%d	Single Digit Day (1-31)	31	1
dd	Double Digit Day (01-31)	31	01
ddd	Short Weekday Name	Sun	Sat
dddd	Full Weekday Name	Sunday	Saturday
%M	Single Digit Month (1-12)	12	2
MM	Double Digit Month (01-12)	12	02
MMM	Short Month Name	Dec	Feb
MMMM	Full Month Name	December	February
%y	Year (0-99)	23	3
yy	Year (00-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.

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" ] )

// 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!