Forum Discussion

SenoirB's avatar
SenoirB
Frequent Visitor
3 years ago
Solved

Convert number with text to number

Hello all, I have a Duration column on a report that I need to transform into total minutes.    The current outputs are a variation of the follow three formats: 15 s 11 m 57 s 2 h 15 m 8 s   I...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi SenoirB 

     

    I modified AntrikshSharma 's solution as below. You can use this code to create a custom column. 

    let result = 
    Text.Replace ( 
        Text.Replace ( 
            Text.Replace ( 
                Text.Replace ( [Time], " ", "" ), 
                "h", "*60+" 
            ), 
            "m", "*1+"
        ), 
        "s", "/60" 
    ) 
in
if Text.EndsWith(result, "+")
then Expression.Evaluate(Text.Range(result, 0, Text.Length(result)-1)) 
else Expression.Evaluate(result)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

AntrikshSharma's avatar
AntrikshSharma
Community Champion
3 years ago

SenoirB Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRVKFaK1QEyDHMVTM0hbKMMQ9NcCyA7FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    ChangedType = Table.TransformColumnTypes ( Source, { { "Time", type text } } ), 
    AddedCustom = 
        Table.AddColumn (
            ChangedType, 
            "Total Minutes", 
            each 
                let
                    RemoveSpace = Text.Replace ( [Time], " ", "" ), 
                    Split = Splitter.SplitTextByCharacterTransition ( { "a" .. "z" }, { "0" .. "9" } ) ( RemoveSpace ), 
                    Transform = 
                        List.Transform (
                            Split, 
                            each 
                                let
                                    Number = Number.From ( Text.Select ( _, { "0" .. "9" } ) ), 
                                    Correction = 
                                        if Text.Contains ( _, "h" ) then Number * 60
                                        else if Text.Contains ( _, "m" ) then Number * 1
                                        else if Text.Contains ( _, "s" ) then Number / 60
                                        else null
                                in
                                    Correction
                        )
                in
                    Number.Round ( List.Sum ( Transform ), 2 ), 
            type number
        )
in
    AddedCustom