Mastering Custom Format Strings in Power Query M

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
  • Simple Date to Text Conversion
  • Using Date.ToText for Custom Formats
  • Table of Custom Format Strings for Date Formatting
• Formatting Time Values
  • Simple Time to Text Conversion
  • Using Time.ToText for Custom Formats
  • Table of Custom Format Strings for Time Formatting
• Formatting DateTime Values
  • Simple DateTime to Text Conversion
  • Using DateTime.ToText for Custom Formats
  • Table of Custom Format Strings for DateTime Formatting
• Formatting DateTimeZone Values
  • Simple DateTimeZone to Text Conversion
  • Using DateTimeZone.ToText for Custom Formats
  • Table of Custom Format Strings for DateTimeZone Formatting
• Formatting Number Values
  • Simple Number to Text Conversion
  • Using Number.ToText for Standard Numeric Formats
  • Table of Standard Numeric Format Strings
  • Using Number.ToText for Custom Numeric Formats
  • Table of Custom Numeric Format Strings
• Conclusion

Formatting Date Values

Date Values are common 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.

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 what if the default format doesn’t suit your needs? One common approach is to extract different components from the date and then reassemble them. However, this method is cumbersome and not recommended.

But the output may not be the right format you need. So what do you do? Many people start out by extracting parts of the date and combining them again after. An approach that requires unnecessary effort.

Using Date.ToText for Custom Formats

Instead, 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:

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.

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.

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:

Format	Description	01:45:30.8859492	23:01:04
t	Short time pattern	01:45	23:01
T	Long time pattern	01:45:30	23:01:04
%h	Hour as a single digit (0-12)	1	11
%H	Hour as a single digit (0-23)	1	23
hh	Hour with leading zero (01 -12)	01	11
HH	Hour with leading zero (00-23)	01	23
%m	Minute as a single digit (0-59)	45	1
mm	Minute with leading zero (00-59)	45	01
%s	Second as a single digit (0-59)	30	4
ss	Second with leading zero (00-59)	30	04
%t	The first character of AM/PM	A	P
tt	The AM/PM designator	AM	PM
%f	Tenths of a second	8	0
ff	Hundredths of a second	88	00
fff	Milliseconds	885	000
ffff	Ten-thousandths of a second	8859	0000
fffff	Hundred thousandths of a second	88594	00000
ffffff	Millionths of a second	885949	000000
fffffff	Ten-millionths of a second	8859492	0000000
%F	If not zero, tenths of a second	8
FF	If not zero, hundredths of a second	88
FFF	If not zero, milliseconds	885
FFFF	If not zero, ten-thousandths of a second	8859
FFFFF	If not zero, hundred-thousandths of a second	88594
FFFFFF	If not zero, millionths of a second	885949
FFFFFFF	Ten millionths of a second	8859492

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.

Format	Description	December 31, 2023 1:30:25	December 29, 2023 22:04:50.36973183
g	General date short time	12/31/2023 1:30 AM	12/29/2023 10:04 PM
G	General date long time	12/31/2023 1:30:25 AM	12/29/2023 10:04:50 PM
s	Short time format	2023-12-31T01:30:25	2023-12-29T22:04:50
u	Universal full format	2023-12-31 01:30:25Z	2023-12-29 22:04:50Z
f	Full date short time	Sunday, December 31, 2023 1:30 AM	Friday, December 29, 2023 10:04 PM
F	Full date long time	Sunday, December 31, 2023 1:30:25 AM	Friday, 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'"
)

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:

Format	Description	12/31/2023 1:30:25 -02:00	12/31/2023 1:30:25 +10:00
%z	UTC offset (hours)	-2	10
zz	UTC offset with leading zero	-02	10
%K, zzz	UTC offset (hours and minutes)	-02:00	+10:00
r, R	RFC1123 pattern	Sun, 31 Dec 2023 03:30:25 GMT	Sat, 30 Dec 2023 15:30:25 GMT
o, O	Round-trip date/time pattern	2023-12-31T01:30:25.0000000-02:00	2023-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:

Format	Category	12,345.6789	12,345	-1.15
C, c	Currency	$12,345.68	$12,345.00	-$1.15
D, d	Decimal		12345
E, e	Exponential	1,23E+10	1,23E+10	-1.150000E+000
F, f	Fixed-Point	12345.68	12345.00	-1.15
G, g	General	123.456.789	12345	-1.15
N, n	Number	12,345.68	12,345.00	-1.15
P, p	Percent	1,234,567.89%	1,234,500.00%	-115.00%
R, r	Round-trip	123.456.789	12345	-1.15
X, x	Hexadecimal		3039

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:

Format	Description	Example	-100	0.01	0.018855	900
0	Zero Placeholder	0.00	-100.00	0.01	0.02	0.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.01	0.02	–
Other	All 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.