CODEWITHSUNDEEP
excelpowerquery
DLark
DLark

Reputation: 17

Convert Text to Minutes and Seconds in Power Query

I have a data set imported into Power Query in Excel that is currently formatted as text (shown in picture), but I need it as time, specifically mm:ss. When I format the columns as time it puts them into hh:mm:ss. I could work with this and convert the hours to minutes and minutes to seconds, but some of the times are over 24 minutes, which returns as an error in Power Query. How can I format the columns as mm:ss?

enter image description here

Upvotes: 0

Views: 431

Answers (1)

Umut K
Umut K

Reputation: 1390

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS2MjZUitUBs0wtwCwjMysjiJiJuZWxiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each #duration(0, Number.FromText(Text.Start([Column1], 2)), Number.FromText(Text.Middle([Column1], 3,2)), Number.FromText(Text.End([Column1], 2)))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}})
in
    #"Changed Type"

Upvotes: 0

Related Questions