Getting time value from string (24+ hours)

[crossover]

Hi! I'm struggling to convert some time data from string to manageable time or duration. Please have a look at the screenshot below. The input data string is engine working hours. When changing the data type, same thing happens for both with Time and Duration. If length is more than 24 hours (a day is marked at the start of the string), conversion produces an error. Otherwise it will convert fine.

Is there a nice way to do this so that 24h+ durations would be managed properly? Ideally I'd want to come up with extended hours (e.g. 1 day + 1 hour would mean 25 hours), but the format is not too important as long as I can do some calculations (I have weekly numbers which I look to divide into days). Thanks!

 

[watkinnc]

Actually, you don't even need to add the extra column; the space instead of the decimal after the day value is the whole issue.  Just go to the Transform tab, select "Replace Values", and change " " (just the space bar, not the quotes) to ".".  Then you can change the column to type duration.  That's it!  Here are the steps after the source step:

#"Replaced Value" = Table.ReplaceValue(Source," ",".",Replacer.ReplaceText,{"utilization_time"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"utilization_time", type duration}})
in
#"Changed Type"

--Nate

[crossover]

Thanks, the duplicated column was indeed more to showcase the before-and-after situation for data type change. But changing blanks for "points" (or full stops they are called?) worked just fine. Wasn't aware this is how full days should be designated in this format.

[BA_Pete]

Hi @watkinnc ,

Thanks for the addition, but the column was only duplicated so OP could see the before and after comparison of the process. As per my post, 'Duplicate Column' was not listed in the summary of steps taken.

As a point of constructive feedback, the general etiquette on the MS Community boards is that you only submit a solution after someone else has provided one if you can offer a significantly enhanced or materially different solution, with reasoning why your alternative might be preferable. Hopefully this will save you some time in future and avoid accusations of post hijacking.

Pete

[BA_Pete · Accepted solution]

Hi @crossover ,

Not a perfect solution (i.e. doesn't go over 24 hours), but a quick solution to enable you to perform calculations.

Copy the following code and paste it over the default code in a new blank query in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcrBCcBACETRVhbPYZnRGHBaEftvI2xy/PzXbVxMsQTuigRgc7XBBcjjC4b8mJ3n/4ALJYfux2Ze", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [utilization_time = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "utilization_time", "utilization_time - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column"," ",".",Replacer.ReplaceText,{"utilization_time - Copy"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"utilization_time - Copy", type duration}})
in
    #"Changed Type"

You can now follow the steps I took, summarised as follows:

1) Replace spaces for full stops

2) Change data type to Duration.

You can perform calculations on this duration column now, and/or change the data type (in a fresh change data type step) to decimal number to give you decimal days.

Pete