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.

## Recommended Reading…

### Power Query Foundations

**Beginners Guide to Power Query**

**List Functions (200+ Examples)**

**Text Functions (150+ Examples)**

**Creating Tables (40+ Examples)**

**Generate the Ultimate Date Table**

### Advanced Topics

**Master List.Generate with Easy Examples**

**Deep Dive into List.Accumulate**

Hi Rick, thanks for this resource. I’m always researching quality resources like this site to learn and improve my M code skills.

I’m not sure about the following: the Extract Text Delimiter method, in my case returns ’20:10:00′ not ’20:10:10′. Could the problem be with the 2nd step? I mean: TruncatedTime = Text.From( TimeValue )

It seems to me that the Text.From function is only returning the hour and minute components, so the rest of the query only works based on this result.

Hi Giovanni,

Interestingly enough I never noticed that. I’ve replaced the method with another one, which you may also like.

Thanks for reporting back to me.

Rick