Convert Text time to minutes "1 Hour 30 minutes" to 90 minutes

[Anonymous]

Hey Team,

I have a column called "Time worked" which looks like this:

I would like to convert to a number to calculate average time spent. 

Example:

2 Hours 38 minutes -> 158 minutes

23 minutes -> 23 minutes

2 Hours 8 minutes -> 128 minutes.

[Anonymous]

Table.AddColumn(#"Added Custom", "minutes", each Expression.Evaluate(Text.Replace(Text.Replace([Column1], " Hours ","*60+"), "minutes","")))

OR

 Table.AddColumn(#"Added Custom", "minutes", each Expression.Evaluate(Text.BeforeDelimiter(Text.Replace([Column1], " Hours ","*60+"), "minutes")))

[smpa01]

@Anonymouscan you please try this

//custom function called qx
let   fx=(input)=>
    Web.Page(
        "<script>
            var str = '"&input&"';
            var res = str.match(/\d+/gm);                      
            document.write(res);
        </script>") {0}[Data]{0}[Children]{1}[Children]{0}[Text]
in
    fx

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLIyC8tKlYwtlDIzcwrLUktVorVAQobo3IVPMCq4IoUlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each qx([Column1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([Custom],",")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Count", each List.Count([Custom.1])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each Text.From(if [Count]=2 then (Number.From([Custom.1]{0})*60)+Number.From([Custom.1]{1}) else [Custom.1]{0})&" "&"minutes"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Custom.1", "Count"})
in
    #"Removed Columns"

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can use this formula for new column. There are probably a better method out to do it, but it will work 🙂

let 
       CreateListHoursMinutes = List.Select(Text.SplitAny([Column1],"Hoursminutes "), each _ <> ""),
       CreateMinutes = if List.Count(CreateListHoursMinutes)= 1 then CreateListHoursMinutes{0}& " minutes" else Text.From(Number.From(CreateListHoursMinutes{0})*60 + Number.From(CreateListHoursMinutes{1})) & " minutes"
    in 
        CreateMinutes

Here the complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLwyC8tKlYwtlDIzcwrLUktVorVAQobo3KhqpAUxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
       CreateListHoursMinutes = List.Select(Text.SplitAny([Column1],"Hoursminutes "), each _ <> ""),
       CreateMinutes = if List.Count(CreateListHoursMinutes)= 1 then CreateListHoursMinutes{0}& " minutes" else Text.From(Number.From(CreateListHoursMinutes{0})*60 + Number.From(CreateListHoursMinutes{1})) & " minutes"
    in 
        CreateMinutes)
in
    #"Added Custom"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy