Time.ToText is a Power Query M function that returns a textual representation of a time value, with optional formatting and culture options. The function returns the text representation of the provided time value.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Time.ToText(
time as nullable time,
optional options as any,
optional culture as nullable text,
) as nullable text| Argument | Attribute | Description |
|---|---|---|
| Time | The time value to transform into text. | |
| Options | Optional | This is an optional argument where you can provide a record parameter with additional instructions. The Format field is used to specify the output format of the transformation. You can also provide a Culture field to ensure the transformation supports local formats. For example: [ Format = “hh:mm:ss tt”, Culture = “nl-NL” ]. If you omit this argument, the default format used will align with the culture of your system, using Culture.Current. |
| Culture | Optional | This is an optional argument used for legacy workflows only. You can specify the culture as a text value here. |
Description
The Time.ToText function transforms a time value into a readable text form. You can influence how the function performs by giving it specific options in a record format. Two key options you can set are Format and Culture. Each has a considerable effect on the function’s output.
Fields in the Record:
- Format: This is a text field that sets the time display format. A full list of supported formats for time values is available later in this article. If you don’t provide this field, or if you set it to null, the function will use a default format, which is determined by the
Culturesetting. - Culture: This option comes into play in two scenarios. First, when you have provided a
Format, theCulturesetting modifies certain aspects of that format. For example, the afternoon time in the United States (“en-US”) will display “PM,” whereas in the Netherlands (“nl-NL”) it won’t display anything. Second, ifFormatis set to null,Culturesets the default time format. IfCultureis also set to null or left blank, the system’s current culture setting will be used.
Examples
Let’s look at some examples to better understand how Time.ToText works.
Basic Conversion
Let’s start with a basic example to illustrate how the Time.ToText function works. Suppose you have a time value #time(12,30,55). You can convert this time to text by using the following code:
// Output: "12:30"
Time.ToText( #time(12,30,55 ) )As shown, the ‘minutes’ part is missing in the output, making it useful when you only want to focus on a specific time frame.
Using Custom Format Strings
To control the output of the function, you can use custom format strings.
Specify Hours, Minutes and Seconds
Where we were missing the seconds component before, we can still add it in our custom format string.
// Output: 12:30:55
Time.ToText( #time(12,30,55 ), [ Format = "hh:mm:ss" ] )Or use the short-hand syntax for the same result:
// Output: 12:30:55
Time.ToText( #time(12,30,55 ), "hh:mm:ss" )Adding AM/PM Designators
Another example shows how you can return the AM/PM designator to your value by including the custom format ‘tt‘ at the end of your formatting string.
// Output: "12:30:55 PM"
Time.ToText( #time(12,30,55 ), [ Format = "hh:mm:ss tt" ] )Supported Format Strings
Customizing the output involves understanding the available custom format strings. Here’s a table that contains the format strings that are supported for time values:
| 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 |
To learn the possibilities and see what other format strings are there, you can have a look at this Overview of all Custom Format Strings.
Considering Culture
The Culture option significantly impacts how time is formatted. For instance, the long-time pattern will differ based on local rules.
AM/PM Designator
Using the Dutch culture (nl-NL), the time is formatted in 24-hour intervals, and there’s no AM/PM designator:
// Output: 23:30:55
Time.ToText( #time(23,30,55 ), [Format = "T", Culture = "nl-NL"] )Switching the culture to American (en-US) will add an AM/PM designator and use a 12-hour clock:
// Output: 11:30:55 PM
Time.ToText( #time(23,30,55 ), [Format = "T", Culture = "nl-NL"] )Here’s some more example where you can see the difference:
The Time.ToText function is a useful function that allows you to convert time into a custom text format. It proves especially useful when working with data where you want to include a time as text. By understanding its custom format strings and culture settings, you can easily adapt the output to suit your specific requirements.
Related articles
Learn more about Time.ToText in the following articles:
- Mastering Custom Format Strings in Power Query
This guide shows how to use custom format strings to format values in the M language. » Read more
Related functions
Other functions related to Time.ToText are:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy