Convert Text in Duration Format

[gssarathkumar]

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 @Ahmedx @v-huijie-msft 

[Ahmedx]

I made two options for you
check it out

[gssarathkumar]

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 · Accepted solution]

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]

[Joe_Barry]

Hi @gssarathkumar 

I haven't tested@bhanu_gautam  solution, please try his first, but here is mine. You may need to adapt the source name.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRR8MgvLSpWMLJU8M3MKy1JLVaK1QGKK7gkVkJZEHEwx8RCITg1OT8vBaLKFKSqWAFuiKkRpiEKhmBJBWMkuVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Input], " ", 1), type text),
    #"Inserted Text After Delimiter1" = Table.AddColumn(#"Inserted Text After Delimiter", "Text After Delimiter.1", each Text.AfterDelimiter([Text After Delimiter], " ", 1), type text),
    #"Inserted Text After Delimiter2" = Table.AddColumn(#"Inserted Text After Delimiter1", "Text After Delimiter.2", each Text.AfterDelimiter([Text After Delimiter.1], " ", 1), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Text After Delimiter2", "1 st", each if Text.Contains([Input], "Day") then Number.FromText(Text.BeforeDelimiter([Input], " Day")) * 1440 else 
if Text.Contains([Input], "Hour") then Number.FromText(Text.BeforeDelimiter([Input], " Hour")) * 60 else 
if Text.Contains([Input], "Minute") then Number.FromText(Text.BeforeDelimiter([Input], " Minute")) * 1 else 
if Text.Contains([Input], "Second") then Number.FromText(Text.BeforeDelimiter([Input], " Second")) * 0.0167 else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "2nd", each if Text.Contains([Text After Delimiter], "Day") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter], " Day")) * 1440 else 
if Text.Contains([Text After Delimiter], "Hour") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter], " Hour")) * 60 else 
if Text.Contains([Text After Delimiter], "Minute") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter], " Minute")) * 1 else 
if Text.Contains([Text After Delimiter], "Second") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter], " Second")) * 0.0167 else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "3rd", each if Text.Contains([Text After Delimiter.1], "Day") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter.1], " Day")) * 1440 else 
if Text.Contains([Text After Delimiter.1], "Hour") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter.1], " Hour")) * 60 else 
if Text.Contains([Text After Delimiter.1], "Minute") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter.1], " Minute")) * 1 else 
if Text.Contains([Text After Delimiter.1], "Second") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter.1], " Second")) * 0.0167 else 0),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "4th", each if Text.Contains([Text After Delimiter.2], "Day") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter.2], " Day")) * 1440 else 
if Text.Contains([Text After Delimiter.2], "Hour") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter.2], " Hour")) * 60 else 
if Text.Contains([Text After Delimiter.2], "Minute") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter.2], " Minute")) * 1 else 
if Text.Contains([Text After Delimiter.2], "Second") then Number.FromText(Text.BeforeDelimiter([Text After Delimiter.2], " Second")) * 0.0167 else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"2nd", type number}, {"1 st", type number}, {"3rd", type number}, {"4th", type number}}),
    #"Inserted Sum" = Table.AddColumn(#"Changed Type1", "Addition", each List.Sum({[2nd], [1 st], [3rd], [4th]}), type number),
    #"Added Custom4" = Table.AddColumn(#"Inserted Sum", "Duration", each let
    TotalMinutes = [Addition],
    TotalSeconds = TotalMinutes * 60,
    Days = Number.IntegerDivide(TotalSeconds, 86400),
    Hours = Number.IntegerDivide(Number.Mod(TotalSeconds, 86400), 3600),
    Minutes = Number.IntegerDivide(Number.Mod(TotalSeconds, 3600), 60),
    Seconds = Number.Mod(TotalSeconds, 60)
in
    #duration(Days, Hours, Minutes, Seconds))
in
    #"Added Custom4"

Hope this helps

Joe

[bhanu_gautam]

@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