Forum Discussion
swinings
4 years agoHelper I
Text to Time
Hey everyone. I'm running into a problem I just can't seem to work out. I am trying to convert text like below to a time function that is usable (i.e. decimals or HH:MM:SS). Instead of ...
- 4 years ago
Borrowing the total duration logic suggested by serpiva64, you can do this in a single add custom column step like this using this formula:
#duration( 0, 0, 0, Expression.Evaluate( Text.Replace( Text.Replace( Text.Replace( Text.Replace([Time], " ", "+"), "h", "*3600" ), "m", "*60" ), "s", "" ) ) )It's slightly easier to understand if you break it into a couple of steps:
Here's the full code for the above. You can paste it into the Advanced Editor in a new blank query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TVAxDsQwCPsK6nxDgZD03lJ1z5Lp/i+dUUjoZsDYmPs+uAxS+R3PB5gHlWvicgG36OsgOwML+ovfCdulRmWDmPekDqq78C0rs9RO+n1RIcFhyt5PH14+125LzRO54fQJDQybsG1ugy8ScaRQGEncIJwpEI4jHMQlZATZEMjWO1DiIglpc7z2T2iVTZOSNCSHhqT/MvKgWvPrC8OVIWf5YC+Xk+lOqf7QwLVTS5J/yNpr4bUhaaMe7PkD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]), #"Added Expression" = Table.AddColumn(Source, "Expression", each Text.Replace( Text.Replace( Text.Replace( Text.Replace([Time], " ", "+"), "h", "*3600" ), "m", "*60" ), "s", "" ), type text), #"Added Duration" = Table.AddColumn(#"Added Expression", "Duration", each #duration(0, 0, 0, Expression.Evaluate([Expression])), type duration) in #"Added Duration"
Anonymous
4 years agoNot applicable
You can split your column by delimiter "h ", then split by "m ", then by "s". Then replace any nulls with "0". Change your new columns to type number. Then you can make a custom column:
= Table.AddColumn(PriorStepOrTableName, "Times", each #time([Time.1], [Time.2], [Time.3]))
--Nate