Forum Discussion
Solved
Unstructured time duration values in column
Hi, I have a data dump in csv that has a number of columns but there is one which is rather tricky. It contains values that are not in a standard format and that is creating a major obstacle in ...
Hi CCAd
This code in Power Query will convert this column into Durations
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZA7EsMgDESvoqFOEQnx8VkYdy7kIrl/GdkeFqXbxz4hhjHSZiTb5/ym/TWSvJ14kvaZ2KjBgc1ilEtwMiRWJ0AzKjV43P+uaKESnZDdQ9GNUMhBzIZYVyxP1KX6WI67WEAlLrsovMNJa1y+5u4vQvcc7z8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Time to first action" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time to first action", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Days", each if Text.Contains([Time to first action], "d") then Text.Start([Time to first action], Text.PositionOf([Time to first action] , "d")) else 0), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Hours", each if [Days] = 0 then if Text.Contains([Time to first action], "h") then Text.Start([Time to first action], Text.PositionOf([Time to first action] , "h")) else 0 else Text.Select(Text.Middle([Time to first action], Text.PositionOf([Time to first action] , "d")+1), {"0".."9"})), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Minutes", each if Text.Contains([Time to first action], "min") then if [Hours] = 0 then Text.Select( [Time to first action], {"0".."9"}) else Text.Select(Text.Middle([Time to first action] , Text.PositionOf([Time to first action] , " ")), {"0".."9"}) else 0), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Days", Int64.Type}, {"Hours", Int64.Type}, {"Minutes", Int64.Type}}), #"Added Custom3" = Table.AddColumn(#"Changed Type1", "Duration", each #duration([Days],[Hours],[Minutes],0)) in #"Added Custom3"In my example file I've left the Days, Hours, Mins columns in place so you can see how the code works, but also in case you wanted to do something else with them.
Just delete them if you don't need them
Regards
Phil
Hi CCAd
This code in Power Query will convert this column into Durations
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZA7EsMgDESvoqFOEQnx8VkYdy7kIrl/GdkeFqXbxz4hhjHSZiTb5/ym/TWSvJ14kvaZ2KjBgc1ilEtwMiRWJ0AzKjV43P+uaKESnZDdQ9GNUMhBzIZYVyxP1KX6WI67WEAlLrsovMNJa1y+5u4vQvcc7z8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Time to first action" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time to first action", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Days", each if Text.Contains([Time to first action], "d") then Text.Start([Time to first action], Text.PositionOf([Time to first action] , "d")) else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Hours", each if [Days] = 0 then
if Text.Contains([Time to first action], "h") then Text.Start([Time to first action], Text.PositionOf([Time to first action] , "h"))
else 0
else Text.Select(Text.Middle([Time to first action], Text.PositionOf([Time to first action] , "d")+1), {"0".."9"})),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Minutes", each if Text.Contains([Time to first action], "min") then
if [Hours] = 0 then Text.Select( [Time to first action], {"0".."9"})
else Text.Select(Text.Middle([Time to first action] , Text.PositionOf([Time to first action] , " ")), {"0".."9"})
else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Days", Int64.Type}, {"Hours", Int64.Type}, {"Minutes", Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type1", "Duration", each #duration([Days],[Hours],[Minutes],0))
in
#"Added Custom3"
In my example file I've left the Days, Hours, Mins columns in place so you can see how the code works, but also in case you wanted to do something else with them.
Just delete them if you don't need them
Regards
Phil
Hi PhilipTreacy
Thank you so much for your help. I have a rather dumber question than before. I already have a bit of code(?) in the advanced editor. How should I add the one you provided? Do you want me to paste whats already there?
