Unstructured time duration values in column

Hi CCAd 

 

Download example PBIX file

 

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