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

Here is another way to solve this:

Expression.Evaluate ( 
    Text.Replace ( 
        Text.Replace ( 
            Text.Replace ( 
                Text.Replace ( [Time], " ", "" ), 
                "h", "*60+" 
            ), 
            "m", "*1+"
        ), 
        "s", "/60" 
    ) 
)

 

SenoirB's avatar
SenoirB
Frequent Visitor
3 years ago

This one was really close but I get an error if there isn't a "s" on the value. Very few of the entries have a value that looks like this:   14 m

I didn't know these were on the there without seeing the errors from the Custom Column. I tried to come up with a solution by Splitting the column and replacing values and couldn't. 

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    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.

    • SenoirB's avatar
      SenoirB
      Frequent Visitor
      3 years ago

      This worked perfectly! Thank you!