mm:ss.00 - converting to a duration

[tomshaw83]

Hi all,

I have a lot of data in the format mm:ss.00 - however at present when I import it to powerbi it doesn't like the look of it as a duration. To convert it I am splitting the data into 3 columns minute / second / milisecond. Creating an additional hour column with a 0 for each row, then merging all four columns back together to get hh:mm:ss.00 - this then can be converted to a duration.

As I have a lot of data in this format, i was wondering if anyone else had encountered this problem, and if there was a simpler more elegant solution.

Thanks

Tom

[tomshaw83]

Excellent, one step solution, thank you

[Anonymous · Accepted solution]

See if this route is preferable

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1MjbXMzY1MjZSitWJVjIxtTI00jM0MjYGc40NrEzN9MwtLC2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    #"Prefisso aggiunto" = Table.TransformColumns(Origine, {{"Colonna1", each "00:" & _, type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Prefisso aggiunto", "Personalizzato", each Duration.From([Colonna1]))
in
    #"Aggiunta colonna personalizzata"