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.

Custom Format Strings are powerful for converting your values into a desired text format. Many tools support them and the Power Query M language is no different.

Functions with a Format parameter (often ending with .ToText) allow you to provide a custom format string. In this article, we will look at the format strings supported by the M language.

Table of contents

Formatting Date Values

The first category of values we’ll look at is that of Date Values in the M language. They are often used for calendars, or any date-related fields and have their own data type, type date.

Although dates have a default format, there are times you’ll need them in a different textual form. This can happen when forming sentences or creating unique keys that include the date.

Tip

For those looking to master various custom date formats, our guide on Date Formatting in Power Query is an invaluable resource.

Simple Date to Text Conversion

Converting a date to text is easy with either Text.From or Date.ToText:

// Both expressions return: "31-01-2024"
Text.From(  #date( 2024, 1, 31 ) ) 
Date.ToText(  #date( 2024, 1, 31 ) ) 

But the output may not be the right format you need. So what do you do?

Many people start by extracting parts of the date and combining them again after. An approach that requires unnecessary effort. Instead you can use a custom date format.

Using Date.ToText for Custom Formats

I recommend using the Date.ToText function with a custom format string to specify the desired format. Here are some quick examples:

// Specify a custom format string like this:
Date.ToText(  #date( 2024, 1, 31 ), [Format = "%d" ] ) // Output: "31"

// Or use shorthand syntax for the same output:
Date.ToText(  #date( 2024, 1, 31 ), "%d" ) // Output: "31"

Table of Custom Format Strings for Date Formatting

Here’s a table of custom format strings supported for date formatting:

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.

Formatting Time Values

Time values are as important as date values in Power Query’s M language. They’re commonly used for logging, scheduling, or any time-based analysis.

While time values have a default format, you might require them in a specific textual format. This can happen when building timelines, aggregating data by time, or creating unique keys that include time.

Simple Time to Text Conversion

You can turn a time value into a text string easily using the Time.ToText function:

// This expression returns: "14:30" 
Time.ToText( #time( 14, 30, 15 ) )

However, this default format misses the ‘seconds’ component and may not always be suitable for your needs.

You might think of breaking down the time into its components (hours, minutes, and seconds) and then assembling them back together in your desired format. But this method can be cumbersome. Instead a format string can simplify your transformation here.

Using Time.ToText for Custom Formats

A more elegant way is to use Time.ToText with a custom format string. This allows you to specify exactly how you want the time value to appear. Here’s an example of how you can specify a custom format string for time values:

// Output: "14:30"
Time.ToText( #time( 14, 30, 15 ), "HH:mm:ss" ) 

In another scenario, you may want to include the AM/PM designator.

// Output: "14:30:15 PM"
Time.ToText( #time( 14, 30, 15 ), "HH:mm:ss tt" ) 

Table of Custom Format Strings for Time Formatting

You can find all custom format strings available for time values in the table below:

FormatDescription01:45:30.885949223:01:04
tShort time pattern01:4523:01
TLong time pattern01:45:3023:01:04
%hHour as a single digit (0-12)111
%HHour as a single digit (0-23)123
hhHour with leading zero (01 -12)0111
HHHour with leading zero (00-23)0123
%mMinute as a single digit (0-59)451
mmMinute with leading zero (00-59)4501
%sSecond as a single digit (0-59)304
ssSecond with leading zero (00-59)3004
%tThe first character of AM/PMAP
ttThe AM/PM designatorAMPM
%fTenths of a second80
ffHundredths of a second8800
fffMilliseconds885000
ffffTen-thousandths of a second88590000
fffffHundred thousandths of a second8859400000
ffffffMillionths of a second885949000000
fffffffTen-millionths of a second88594920000000
%FIf not zero, tenths of a second8
FFIf not zero, hundredths of a second88
FFFIf not zero, milliseconds885
FFFFIf not zero, ten-thousandths of a second8859
FFFFFIf not zero, hundred-thousandths of a second88594
FFFFFFIf not zero, millionths of a second885949
FFFFFFFTen millionths of a second8859492

Formatting DateTime Values

DateTime values are a combination of date and time, often used for capturing exact moments, logging activities, or defining schedules.

Similar to date and time, DateTime values also have their default format. But, just like with date and time, there might be cases where you need to present them differently. This could be for forming readable strings, reporting, or constructing keys that need both date and time.

Simple DateTime to Text Conversion

The default way to convert a DateTime value to text is through the DateTime.ToText function:

// This expression returns: "2024-01-31 14:30:15"
DateTime.ToText( #datetime( 2024, 1, 31, 14, 30, 15 ) )

This works well for many scenarios. But what if you need a specific format? One approach is to manually break down the DateTime into its date and time parts and then piece them back together. But as mentioned before, this is laborious and prone to errors.

Using DateTime.ToText for Custom Formats

A more efficient approach is to use DateTime.ToText along with a custom format string. This allows you to define the textual representation in a single format string.

Here’s how you can return the ‘Full Date short time’ format:

// Output: "31 January 2024 14:30:15"
DateTime.ToText( #datetime(2024, 1, 31, 14, 30, 15), "f" )

But you are free to make use of any of the regular date or time expressions.

// Output: "Today is January 31st 2024 and currently it is 2PM."
DateTime.ToText( 
  #datetime( 2024, 1, 31, 14, 30, 15 ), 
  "'Today is 'MMMM d'st' yyyy 'and currently it is' htt." 
)

Table of Custom Format Strings for DateTime Formatting

The custom format strings you can use for DateTime values are all of those found in the date and time section, and additionally, you can use the new ones provided in the below table.

FormatDescriptionDecember 31, 2023 1:30:25December 29, 2023 22:04:50.36973183
gGeneral date short time12/31/2023 1:30 AM12/29/2023 10:04 PM
GGeneral date long time12/31/2023 1:30:25 AM12/29/2023 10:04:50 PM
sShort time format2023-12-31T01:30:252023-12-29T22:04:50
uUniversal full format2023-12-31 01:30:25Z2023-12-29 22:04:50Z
fFull date short timeSunday, December 31, 2023 1:30 AMFriday, December 29, 2023 10:04 PM
FFull date long timeSunday, December 31, 2023 1:30:25 AMFriday, December 29, 2023 10:04:50 PM

Formatting DateTimeZone Values

DateTimeZone values are useful when you need to track both the date and time along with the time zone. This data type is important for events involving international time differences, like scheduling global team meetings or logging events across time zones.

Simple DateTimeZone to Text Conversion

Converting a DateTimeZone value to text is straightforward by using the DateTimeZone.ToText function:

// This expression returns: "31/01/2024 14:30:15 +02:30"
DateTimeZone.ToText( #datetimezone( 2024, 1, 31, 14, 30, 15, 2, 30 ) )

The output includes the date, the time, and the time zone offset. For most people, the default output will suffice. But what if you need a custom format?

Using DateTimeZone.ToText for Custom Formats

You can use custom format strings to change how the DateTimeZone value is displayed. Much like Date.ToText and Time.ToText, you can pass a custom format string to DateTimeZone.ToText.

For instance, when you want to return time in the RFC1123 International format you could write:

// Output: "Wed, 31 Jan 2024 14:30:15 GMT"
DateTimeZone.ToText(
  #datetimezone( 2024, 1, 31, 14, 30, 15, 0, 0 ), 
  "r"
)

The output provides a string with ‘GMT’ at the end by default. In case you want to return a value that shows ‘UTC’ instead you can’t use a default format string. However, you are free to combine any of the custom format strings related to Date, Time or DateTime to get the output you desire.

// Output: "2024-01-31 14:30:15 UTC"
DateTimeZone.ToText(
  #datetimezone( 2024, 1, 31, 14, 30, 15, 0, 0 ), 
  "yyyy-MM-dd HH:mm:ss 'UTC'"
)

Note

Here, the ‘UTC’ text is hardcoded because the format strings don’t natively support time zone names other than ‘GMT’.

Table of Custom Format Strings for DateTimeZone Formatting

You can use almost all the custom format strings applicable to date and time values, with the addition of special formatting for time zone offset:

FormatDescription12/31/2023 1:30:25 -02:0012/31/2023 1:30:25 +10:00
%zUTC offset (hours)-210
zzUTC offset with leading zero-0210
%K, zzzUTC offset (hours and minutes)-02:00+10:00
r, RRFC1123 patternSun, 31 Dec 2023 03:30:25 GMTSat, 30 Dec 2023 15:30:25 GMT
o, ORound-trip date/time pattern2023-12-31T01:30:25.0000000-02:002023-12-31T01:30:25.0000000+10:00

Formatting Number Values

Numbers have a dedicated data type in M and a myriad of ways to represent them. Depending on your needs, you might want to showcase numbers as currency, percentages, or even scientific notation.

While numbers have a default representation, there will be instances where you need them in a specific textual format, especially when creating custom keys or including them in some text.

Simple Number to Text Conversion

Converting a number to text is straightforward using either Text.From or Number.ToText:

// Both expressions return: "12345"
Text.From( 12345 )
Number.ToText( 12345 )

While these functions provide a default textual representation, there are times when a specific format is required. Some might think of converting numbers using string manipulation, but this is not the most efficient way.

Using Number.ToText for Standard Numeric Formats

A better solution is the Number.ToText function combined with a custom format string. This method lets you depict numbers in the desired format with ease. Here are some examples.

You can format your number value as currency by writing:

// Output: "$1,234.56"
Number.ToText( 1234.56, "C" ) 

// Or return 5 decimals, output: "$1,234.56000"
Number.ToText( 1234.56, "C5" ) 

The second format string contains a precision specifier indicating the number of decimals to return. Likewise, you can represent the number as a percentage by writing:

// Output: "56.00%"
Number.ToText( 0.56, "P" ) 

// Or return 5 decimals, output: "56.00000%"
Number.ToText( 0.56, "P5" ) 

Table of Standard Numeric Format Strings

For a comprehensive understanding, here’s a table of standard format strings for number formatting:

FormatCategory12,345.678912,345-1.15
C, cCurrency$12,345.68$12,345.00-$1.15
D, dDecimal 12345
E, eExponential1,23E+101,23E+10-1.150000E+000
F, fFixed-Point12345.6812345.00-1.15
G, gGeneral123.456.78912345-1.15
N, nNumber12,345.6812,345.00-1.15
P, pPercent1,234,567.89%1,234,500.00%-115.00%
R, rRound-trip123.456.78912345-1.15
X, xHexadecimal 3039

Tip

For a more thorough explanation and examples using the precision specifier, make sure to check out this guide to Number.ToText.

Using Number.ToText for Custom Numeric Formats

For more specific formatting, you can also make use of Custom Numeric Format Strings. These formatting strings allow you to be more precise in the desired output format.

Suppose you want to format a number value to include a description of the degrees in Celsius. Here’s how you can do it:

// Output: "26.7° Celsius"
Number.ToText( 26.7, "0.0° Celsius")

For a series of limited items, you want to format the number 45 as: “Item #0045”.

// Output: "Item #0045"
Number.ToText( 45, "#Item \#0000")

Table of Custom Numeric Format Strings

As a reference, below you can find a table with custom format strings for number formatting:

FormatDescriptionExample-1000.010.018855900
0Zero Placeholder0.00-100.000.010.020.00
#Digit Placeholder#.###-100.01.019
,Group Separator##,#-100
%Percentage Placeholder##.0%-10000.0%1.0%1.9%.0%
Per mile placeholder#0.00‰-100000.00‰10.00‰18.86‰0.00‰
\Escape character\#0.00\#-#100.00##0.01##0.02##0.00#
‘string’ or “string”Literal string delimiter#.#” degrees”-100 degrees degrees degrees degrees
;Section separator#0.0#;(#0.0#);-(100.0)0.010.02
OtherAll other characters# °-100 ° ° ° °

Note

Always consider the regional settings when formatting numbers. Different regions might interpret symbols like the comma , and period . differently.

To respect the desired cultural considerations, you can specify a Culture code. Read more on this in the Number.ToText article.

Conclusion

Custom format strings are an invaluable tool for working with text representations of various data types in the M language. Whether you are dealing with dates, times, or more complex DateTimeZone values, custom format strings provide a way to easily convert these data types into your desired textual format.

They offer a more efficient alternative to manually disassembling and reassembling your code, saving you both time and reducing the risk of errors. With a wide range of formatting options available, you can achieve almost any text output you want.

Share on:

Leave a comment

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