Forum Discussion
Text to Time
- 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"
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"This is how you should format code and examples in this forum, btw. I wish the admins would start pushing back on unformatted code dumps.