#time - Time Function | Power Query M

#time is a Power Query M function that creates a time value from numbers representing the hour, minute, and (fractional) second.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

#time(
   hour as number,
   minute as number,
   second as number,
) as time

Description

The #time function in Power Query creates a time value from specified hours, minutes, and (fractional) seconds. The criteria for each component are:

Hours: 0 to 24 (if 24, minutes and seconds must be 0)
Minutes: 0 to 59
Seconds: 0 to 59

If your value does not meet these criteria, the function returns an expression error saying: “The Time operation failed because the resulting value falls outside the range of allowed values”.

Examples

Let’s see how the #time function works.

Creating a Simple Time Value

If you want to create a time value for 23:11:05 (11:11:05 PM), you can use the following code:

#time( 23, 11, 5 )

This code specifies 23 hours, 11 minutes, and 5 seconds.

Working with Fractional Seconds

You can also include fractional seconds in your time value. For example, to create the time 23:11:05.425 (11:11:05 PM with 425 milliseconds), use this code:

#time( 23, 11, 5.425 )

Handling 24 Hours

The #time function allows you to use 24 as the hour component to represent midnight. Here’s how you can create a time value for midnight:

#time( 24, 0, 0 ) // Returns 00:00:00

This code will return 00:00:00, which is equivalent to midnight.

Errors with Invalid Time Values

However, if you include minutes or seconds when the hour component is 24, Power Query will throw an error because the time value would be invalid. For example:

/* Output: "Expression.Error: The Time operation failed because the 
   resulting value falls outside the range of allowed values" */ 

#time( 24, 0, 5 )

This code will result in an error because 24:00:05 is not a valid time.

Extracting Time Components

You can extract individual components (hours, minutes, and seconds) from a time value using specific functions:

Time.Second( #time( 5, 10, 30 ) ) // Returns 30

Time.Minute( #time( 5, 10, 30 ) ) // Returns 10

Time.Hour( #time( 5, 10, 30 ) ) // Returns 5

Other functions related to #time are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/sharptime