Remove Milliseconds from Time Values in Power Query M

Time values in Power Query comprise not only of hours, minutes and seconds, but can also contain fractions of a second. And the more detailed your values are, the more storage it requires in your model. A good strategy to optimize your model is therefore to reduce the level of detail of your values where possible. To help you do this, this article delves into techniques to remove the milliseconds portion of time values.

Let’s say we have a time value of:

#time( 20, 10, 10.4443338 ) // Returns: 20:10:10.4443338

You can see a seconds portion of 10.4443338. And for most scenarios, the level of detail of this time value is too high. It would be good to truncate the time value to only include hours, minutes and seconds. So, how can you do that?

Time.ToText method

The first approach makes use of the Time.ToText function. This function takes a time value as input and allows you to provide a format string that specifies the output format. We could use the format "hh:mm:ss" to specify just hours, minutes and seconds. After turning the time value into text with the right format, we then restore it using the Time.From function. The solution then looks as follows:

// Output: #time( 20, 10, 10 )
let
    TimeValue = #time( 20, 10, 10.44433382),
    TruncatedTime = Time.ToText( TimeValue, "hh:mm:ss"),
    NewTimeValue = Time.From( TruncatedTime )
in
    NewTimeValue

#time method

Another approach to achieve the same results is manually extracting the hours, minutes and seconds portion of the time value. If we provide those to the #time function, we can directly build our time value. To extract the different components we can make use of the Time.Hour, Time.Minute and Time.Second functions as follows:

// Output: #time( 20, 10, 10 )
let
    TimeValue = #time( 20, 10, 10.44433382),
    Hours = Time.Hour( TimeValue ),
    Minutes = Time.Minute( TimeValue ), 
    Seconds = Int64.From( Time.Second( TimeValue ) ),
    NewTimeValue = #time( Hours, Minutes, Seconds )
in
    NewTimeValue

Normalize Time Value

A third approach (suggested by Bill Szysz) uses the Number.IntegerDivide function. This approach first turns the time value into a number by using the Number.From function. It then converts the fractional day number to a total number of seconds since 86400 seconds make up a full day. Number.IntegerDivide(..., 1) effectively removes the fractional part of the seconds, leaving you with an integer that represents the total number of seconds, rounded down to the nearest whole number.

IntegerDivide / 86400 converts the total number of seconds back into a fractional day number, but now without the fractional seconds that were removed earlier. Finally the Time.From function converts the fractional day number back to a time value.

// Output: #time( 20, 10, 10 )
let
  TimeValue = #time(20, 10, 10.44433382), 
  // Convert the time value to a number, where 1 represents a full day
  TimeAsNumber = Number.From(TimeValue), 
  /* Convert the fractional day number to total seconds and remove 
      fractional seconds by using integer division */
  IntegerDivide = Number.IntegerDivide(TimeAsNumber * 86400, 1), 
  // Convert the integer number of seconds back to a fractional day number
  RestoreNumber = IntegerDivide / 86400, 
  // Convert the fractional day number to a time value with whole seconds
  NewTimeValue = Time.From(RestoreNumber)
  NewTimeValue

Remove milliseconds using #duration

We can also extract the milliseconds portion of the time value by storing it in the #duration function. Once we have that amount, we can subtract this from the original value. First we extract the seconds portion using Time.Second. To remove the full seconds and leave us with the milliseconds, we can make use of the Number.Mod function. The #duration function can store these milliseconds in the fourth argument. Finally, we can subtract this duration value from the original time value, to end up with only a time value. Here’s what the code looks like:

let
    TimeValue = #time( 20, 10, 10.4443338 ),
    Seconds = Time.Second( TimeValue ),
    Milliseconds = Number.Mod( Seconds, 1 ),
    MillisecondsDuration = #duration( 0, 0, 0, Milliseconds ),
    NewTimeValue = TimeValue - MillisecondsDuration
in
    NewTimeValue

Conclusion

As you can see, there is a range of approaches available to remove the milliseconds portion from a second. Whereas there’s no easy built in function, with a bit of creativity you can get the desired end result. This should help in reducing the level of detail in your column and the amount of storage your Power BI model requires.