Creation of a Timestamp (HH:MM) from Text

[EricKautz]

I have an excel report that I want to pull timing data from. Unfortunately the column of data that has the time is in a text format below:

1hr 3min

0hr 23min

0hr 23min)

doing a text.remove won't help as it will combine all the numbers together and won't be able to differentiate which numbers are the hours and minutes. Right function would work for minutes, but can't for hours as that number can be infinite. How can I get the data from the above reference into a formatted timestamp (01:03, 00:23, etc)

[Chewdata · Accepted solution]

if the ) at the end is accidental, then you can use this.
1. remove the min
2. split column on "hr "
3. combine into a timeformat.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMswoUjDOzcxTitWJVjIAcoyw8WIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","min","",Replacer.ReplaceText,{"Time"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Time", Splitter.SplitTextByDelimiter("hr ", QuoteStyle.Csv), {"Hours", "Minutes"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Hours", Int64.Type}, {"Minutes", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Timestamp", each #time([Hours],[Minutes],0), type time)
in
#"Added Custom"

[HotChilli]

replace 'hr ' with':'

Remove the 'min'.

Add a zero at the front if required. Then construct a #time or convert to the appropriate type