Forum Discussion

gssarathkumar's avatar
gssarathkumar
Icon for Helper I rankHelper I
2 years ago
Solved

Convert Text in Duration Format

Hi,   I have the below Input table and I intend to get the Required Output column.     Kindly help me on this solution   amitchandak Greg_Deckler lbendlin DesktopOwl Goodlytics4U HelpMe ...
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    Is this what you are looking for?

     

    [
a = Text.Select([Input],{"0".."9","D","H","M","S"," "}),
    b= Text.Split(a," "),
    d = Number.From(try b{List.PositionOf(b,"D")-1} otherwise 0),
    h = Number.From(try b{List.PositionOf(b,"H")-1} otherwise 0),
    m = Number.From(try b{List.PositionOf(b,"M")-1} otherwise 0),
    s= Number.From(try b{List.PositionOf(b,"S")-1} otherwise 0),
    final = #duration(d,h,m,s)][final]

     

     

     

Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
2 years ago

I made two options for you
check it out

gssarathkumar's avatar
gssarathkumar
Icon for Helper I rankHelper I
2 years ago

Hi Ahmedx ,

 

Thanks for your response.

 

I tried the options, it worked. However, when i try to convert it into Duration, some of the rows throw error.

It's due to the higher value in hours. Can you please help me to get the Input transformed in the below format?

d.hh:mm:ss

 

 

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    Is this what you are looking for?

     

    [
a = Text.Select([Input],{"0".."9","D","H","M","S"," "}),
    b= Text.Split(a," "),
    d = Number.From(try b{List.PositionOf(b,"D")-1} otherwise 0),
    h = Number.From(try b{List.PositionOf(b,"H")-1} otherwise 0),
    m = Number.From(try b{List.PositionOf(b,"M")-1} otherwise 0),
    s= Number.From(try b{List.PositionOf(b,"S")-1} otherwise 0),
    final = #duration(d,h,m,s)][final]