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 ...
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]
Hi 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