Forum Discussion

gssarathkumar's avatar
gssarathkumar
Helper I
1 year 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
    1 year 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]

     

     

     

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

gssarathkumar , You can achieve this in Power query using below m code

 

= let
    input = [Input],
    days = if Text.Contains(input, "Day") then Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter(input, "Day"), " ")) else 0,
    hours = if Text.Contains(input, "Hour") then Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter(input, "Hour"), " ")) else 0,
    minutes = if Text.Contains(input, "Minute") then Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter(input, "Minute"), " ")) else 0,
    seconds = if Text.Contains(input, "Second") then Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter(input, "Second"), " ")) else 0,
    totalHours = days * 24 + hours,
    formattedTime = Text.PadStart(Text.From(totalHours), 2, "0") & ":" & Text.PadStart(Text.From(minutes), 2, "0") & ":" & Text.PadStart(Text.From(seconds), 2, "0")
in
    formattedTime