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"
Greg_Deckler
4 years agoCommunity Champion
swinings You should be able to modify this to parse your text duration column:
Text Duration Conversion - Microsoft Power BI Community
Then you probably want something like this:
Chelsie Eiden's Duration - Microsoft Power BI Community