Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

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

Extract Text Delimiter method

A third approach is by using the Text.BeforeDelimiter function. This approach first turns the time value into text by using the Text.From function. It then extracts all values before the decimal separator. Finally the Time.From function turns the text value into a time value.

// Output: #time( 20, 10, 10 )
let
    TimeValue = #time( 20, 10, 10.44433382),
    TruncatedTime = Text.From( TimeValue ),
    BeforeDelimiter = Text.BeforeDelimiter( TruncatedTime, "." ),
    NewTimeValue = Time.From( BeforeDelimiter )
in
    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.

Share on:
  1. Hello Rick,
    It’s my first time getting in touch with you so let me tell first how I appreciate so much the huge amount of work you put into making M easy to grasp. I now fell like I’m starting to master it and you are a big share of my online resources. So thank you so much !
    That said, may I suggest you double check the #time method ? It appears to me that Time.Second keeps the fractionnal part of the seconds and then pass it back again to the #time function…
    I guess something like

    Seconds = Number.Round(Time.Second( TimeValue ), 0) 

    would do the job ?
    Anyway, thank you for giving me the oppportunity to improve my knowledge of M on a regular basis.
    Have a great week-end,
    Marc

    Reply
    • Marc!

      Thanks for pointing that one out, just updated it. I intended to provide the following code:

      Int64.From( Time.Second( TimeValue ) )
      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.