Unstructured time duration values in column

[CCAd]

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 my calculations. A sample is below. Is there anyway that I can have this data converted into a uniform and structured standard?

Time to first action

9h 29min

20h 21min

48min

1h 7min

29min

12h 35min

1h 37min

14h 3min

17h 56min

1h 18min

12h 37min

1h 24min

3h 1min

18h 4min

2d 11h

2d 16h

2d 15h

44min

2h 43min

12h 12min

15h 24min

15h 37min

15h 46min

18h 12min

20h 26min

2min

Thanks,

Ad

[PhilipTreacy]

Hi @CCAd 

No worries.

If you paste your code here I'll try to integrate my code into it.

If you can also supply a sample of the full data that the query is working on that will help.

regards

Phil

[CCAd]

@PhilipTreacy 

Mate, your code worked just awesome. I was able to integrate it with the pre-existing one. But again, thank you so much!

[PhilipTreacy · Accepted solution]

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

[CCAd]

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?