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
- Formatting Time Values
- Formatting DateTime Values
- Formatting DateTimeZone Values
- Formatting Number Values
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
// 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|
|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.|
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:
|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|
|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
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
// 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:
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:
|‰||Per mile placeholder||#0.00‰||-100000.00‰||10.00‰||18.86‰||0.00‰|
|‘string’ or “string”||Literal string delimiter||#.#” degrees”||-100 degrees||degrees||degrees||degrees|
|Other||All other characters||# °||-100 °||°||°||°|
Note: Always consider the regional settings when formatting numbers. Different regions might interpret symbols like the comma
, and period
To respect the desired cultural considerations, you can specify a Culture code. Read more on this in the Number.ToText article.
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.