Convert text without consistent formatting to duration

[Emmerson]

In Power BI, I have a text column that contains what should be a duration, only it's in text format so I can not format it as a 'duration'. Examples are: "28 Hours", "5 Hours 20 Minutes", "1 Hour", "0 Seconds", and "50 Hours 1 Minute". So you'll see there's no consistent HH:MM:SS format. How do I convert the text to a proper duration format?

[ppm1]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrJQ8MgvLSpWitWJVjJVyACxFYwUcjPzSktSIaKGpjCugqGBQnFqcn5eClTGCKrBECqsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DurationAsText = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DurationAsText", type text}}),
    #"Lowercased Text" = Table.TransformColumns(#"Changed Type",{{"DurationAsText", Text.Lower, type text}}),
    #"Added Custom" = Table.AddColumn(#"Lowercased Text", "Duration", each let 
hmslist = {{"hour", 24}, {"minute", 24*60}, {"second", 24*60*60}},
splittext = Text.Split([DurationAsText], " "),
partialcombine = List.Transform(List.Split(splittext, 2), each Text.Combine(_)),
hms = let input = partialcombine in List.Transform(hmslist, (w)=> try Number.From(Text.Select(List.Select(input, (y)=> Text.Contains(y, w{0})){0}, {"0".."9"})) * (1/ w{1}) otherwise 0),
result = List.Sum(hms)
in 
result),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}})
in
    #"Changed Type1"

Pat

[Emmerson]

Thank you! Can you tell me how I can add a column to my existing table in power query? The column which contains the text is 'Result.u_time_actual_time'. Thanks! 

[ppm1 · Accepted solution]

The #"Added Custom" step is the key one in the M provided. First convert your text column to lower case using that option on Transform/Format. Then on the Add Column tab, choose Custom Column and paste in the pop-up window.

let 
hmslist = {{"hour", 24}, {"minute", 24*60}, {"second", 24*60*60}},
splittext = Text.Split([Result.u_time_actual_time], " "),
partialcombine = List.Transform(List.Split(splittext, 2), each Text.Combine(_)),
hms = let input = partialcombine in List.Transform(hmslist, (w)=> try Number.From(Text.Select(List.Select(input, (y)=> Text.Contains(y, w{0})){0}, {"0".."9"})) * (1/ w{1}) otherwise 0),
result = List.Sum(hms)
in 
result

Pat

[ppm1]

@Emmerson Did this work for you? If so, please mark it as a solution.

Pat