Convert ss.xxx to mm.ss.xxx

[aaron797]

How do you convert ss.xxx to mm:ss:xxx in query editor?

For example, my data is coming in like 229.19, i can convert this to 0.00:03:49.190000 using #duration.

#duration(0,0,0,[Cumulative_Time])

However i would like a solution that doesn't include days and hours, and rounds to nearest 3 decimals.

I am trying to achieve "03:49.190"

[v-eqin-msft · Accepted solution]

Hi @aaron797 ,

To my knowledge, Duration type in Power Query does not support various format as you expected.

You could use Duration.Minutes() and Duration.Seconds() and then combine them to get the format as mm:ss.xxx.

The final output is shown below: (But it should be noted that the final data type of the column is Text.)

Here is the whole M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKy1DO0VIrViVYyNzDQMzZRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cumulative_Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cumulative_Time", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each #duration(0,0,0,[Cumulative_Time])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Minute", each Number.ToText( Duration.Minutes([Duration]),"0#")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Second", each Number.ToText( Duration.Seconds([Duration]),"##.000")),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "New Format", each [Minute]&":"&[Second]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Minute", "Second"})
in
    #"Removed Columns"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

You can wrap your #duration with Duration.TotalMinutes to get minutes as a decimal.  A good practice is to store durations as a decimal number and then use custom format strings to display it how you want once you've done any math.

Pat