Forum Discussion
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 Ahmedx v-huijie-msft
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]
5 Replies
gssarathkumar , You can achieve this in Power query using below m code
= letinput = [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")informattedTimeHi gssarathkumar
I haven't testedbhanu_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
I made two options for you
check it outHi 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
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]
