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