Forum Discussion
Solved
Text to Minutes, Seconds, Milliseconds Format
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,...
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
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:
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")