Text to Minutes, Seconds, Milliseconds Format

[nicoag98]

Hi all, I have a text column with circuit lap times (Minutes, Seconds, Milliseconds)

You know if it is possible to convert this data to a duration format with milliseconds in order to make averages, etc?

[IronBI]

My solution was the following:
I had values for Q1, Q2, Q3 with the following format:
1:25.471
In powerquery I applied the following:

    #"ParseQ1" = Table.TransformColumns(
        #"Replaced Value2",
        {
            {"q1", each 
                if Text.Trim(_) = "" or _ = null then 
                    null 
                else 
                    let 
                        secondsAndMilliseconds = Text.Split(_, "."),
                        seconds = Int64.From(Text.Split(secondsAndMilliseconds{0}, ":"){1}),
                        minutes = Int64.From(Text.Split(secondsAndMilliseconds{0}, ":"){0}),
                        milliseconds = Number.FromText("0." & secondsAndMilliseconds{1}, "en-US")
                    in 
                        #duration(0, 0, minutes, seconds + milliseconds)
            , type nullable duration}
        }
    ),

Then I applied dynamic formatting in the measure (like fastest lap, average time, etc):

VAR TotalSeconds = SELECTEDMEASURE() * 24 * 60 * 60  // Convert days to total seconds
VAR mmss = FORMAT(SELECTEDMEASURE(), "hh:MM:ss")  // Format hours, minutes, and seconds
VAR Milliseconds = INT((TotalSeconds - INT(TotalSeconds)) * 1000)  // Calculate milliseconds accurately

RETURN
    mmss & "." & FORMAT(Milliseconds, "000")  // Ensure milliseconds are always 3 digits

Now my results look like this:

[IronBI]

Noticed sometimes it worked, sometimes it didn't, so I kept trying and with the help of Chatgpt and experimentation, finally got if working:

VAR TotalSeconds = SELECTEDMEASURE() * 24 * 60 * 60  // Convert fractional day to total seconds
VAR Minutes = INT(TotalSeconds / 60)  // Extract minutes
VAR Seconds = INT(MOD(TotalSeconds, 60))  // Extract seconds
VAR Milliseconds = INT((TotalSeconds - INT(TotalSeconds)) * 1000)  // Extract milliseconds

RETURN
    FORMAT(Minutes, "00") & ":" & 
    FORMAT(Seconds, "00") & UNICHAR(8228) & 
    FORMAT(Milliseconds, "000")

[therealfzoccara]

From a duration column (displayed in visuals as a decimal) I'd create a measure, this measure aggregate the duration values and display it as a readable column, here the measure code:

Timing =
VAR dur = AVERAGE('Fact Tempi'[Durata])
VAR hours = INT ( MOD ( dur * 24 , 24 ) )
VAR minutes = INT ( MOD ( dur * 24 * 60, 60 ) )
VAR seconds = INT ( MOD( dur * 24 * 60 * 60, 60))
VAR millis  = INT ( MOD ( dur * 24 * 60 * 60 * 1000, 1000 ))

VAR hoursText =
    IF (
        LEN ( hours ) = 1,
        CONCATENATE ( "0", hours ),
        CONCATENATE ( "", hours )
    )
VAR minutesText =
    IF (
        LEN ( minutes ) = 1,
        CONCATENATE ( "0", minutes ),
        CONCATENATE ( "", minutes )
    )
VAR secondsText =
    IF (
        LEN ( seconds ) = 1,
        CONCATENATE ( "0", seconds ),
        CONCATENATE ( "", seconds )
    )
VAR millisText =
    IF (
        LEN ( millis ) = 3,
        CONCATENATE ( "", millis ),
        IF (
            LEN ( millis ) = 1,
            CONCATENATE ( "00", millis ),
            CONCATENATE ( "0", millis )
        )
    )
RETURN
    hoursText & ":" & minutesText & ":" & secondsText & "." & millisText

[ppm1 · Accepted solution]

First add a prefix of 00: as text to your column (Transform tab, Format button), then convert it to type Duration. When you load your data, it will convert to a decimal (in days). From there, do your measures/aggregations and then FORMAT it at the end, if desired. See this article for more details.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[nicoag98]

Thank Pat for your reply,

I found it very usefull, just one thing more, it is possible to FORMAT the decimal days again to Minutes,seconds, milliseconds?

[ppm1]

Yes. You have a few options.

1. Add a custom column in your query with the Duration.ToText() function from your new Duration column. Then change type to text.

2. Add a DAX column with this expression (FORMAT doesn't support .fff like you can do in Excel, so have to calculate the mSec and append).

DAX Column mSec =
VAR dur = 'T1'[Time] 
VAR mmss =
    FORMAT ( dur, "hh:MM:ss" )
VAR fff =
    INT ( MOD ( dur * 24 * 60 * 60, 1 ) * 1000 )
RETURN
    mmss & "." & fff

3. Measure with the same as #2. I would do this one, so you don't have to store the extra column in your data (and you can get a total, etc.).

Sum With mSec =
VAR TimeSum =
    SUM ( 'T1'[Time] )
VAR mmss =
    FORMAT ( TimeSum, "hh:MM:ss" )
VAR fff =
    INT ( MOD ( TimeSum * 24 * 60 * 60, 1 ) * 1000 )
RETURN
    mmss & "." & fff

Pat

[nicoag98]

@ppm1 

[Greg_Deckler]

@nicoag98 Sure, it is going to be a slight variation on this: Duration to Seconds Converter - Microsoft Power BI Community

This might be helpful as well: Milliseconds Duration - Microsoft Power BI Community