Convert Date/time/timezone field to duration.

[liamcol1]

I am trying to convert this data type to duration but I get an error. Is there a workaround for this? 

[OwenAuger]

Hi @liamcol1 

It looks like you have datetime/timezone values, and you want to convert each to a duration equal to the the time since the Unix epoch (1970-01-01 00:00 UTC).

To my knowledge there is no built-in function for this in the Power Query interface, but you can add a step with the following M code:

= let UnixEpoch = #datetimezone(1970,1,1,0,0,0,0,0) in Table.TransformColumns(PreviousStepName, {"TimeWorked", each _ - UnixEpoch, type duration})

where PreviousStepName is the name of the previous step.

Does that work for you?

Regards,

Owen

[liamcol1]

this is actually the field i need, whihc doesn't have the time zone so would it be easier? 

[OwenAuger]

Thanks 🙂 If there is no timezone, there is only a slight change to the code.

Also, I would recommend you make sure the column type is set to Date/Time before converting to duration.

Here is a sample query including the final ConvertToDuration step assuming you have Date/Time values:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31De0NDdQMDC2MjBQitWJVgIThnBxCxzihlYmptjETVHE0SThliCEDKyMgUKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UTotalTimeWorkedInHours = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"UTotalTimeWorkedInHours", type datetime}}, "en-NZ"),
    ConvertToDuration = let UnixEpoch = #datetime(1970,1,1,0,0,0) in Table.TransformColumns(#"Changed Type with Locale", {"UTotalTimeWorkedInHours", each _ - UnixEpoch, type duration})
in
    ConvertToDuration

[liamcol1]

That calculation is already done, this is the field I need to convert to hours 

[TheoC]

Hi @liamcol1 

Apply the following:

1. Convert column to Text
2. Add column using Delimiters - extract Between Delimeters - and use space as delimeter. Ensure to keep the time values only and call it new column "Duration"
3. Then add Custom Column as per below:

try

let _1 = List.Transform ( Text.Split ( [Duration] , ":" ), each Number.FromText(_) ) in #duration ( 0 , 0 , ( if List.Count (_1) = 3 then _1{0} * 60 else 0 ) + _1{1} , _1{2}) otherwise null

This will get you what you're after.

Cheers,
Theo

[liamcol1]

Hi there, I have tried it but am getting null for every row. Does the time value have to be seperated from AM/PM too? 

[TheoC]

Yes, correct. If you follow the steps, you'll get the outcome you need.  

1. Convert your column to text "ABC" by clicking on the top left button on the specific column (i.e.  your Duration column)

2. Go to Add Columns in the ribbon.  Then click on Extract.

3. Under Extract, click on "Text Between Delimeters". Put a space (i.e. " ") in the Start delimeter. Then, put another space " " in the End delimeter (as per below) then click OK:

4. Convert the Duration column to ABC if you have not done so already.

5. Add a Custom Column and copy in the below:

try

let _1 = List.Transform ( Text.Split ( [Duration] , ":" ), each Number.FromText(_) ) in #duration ( 0 , 0 , ( if List.Count (_1) = 3 then _1{0} * 60 else 0 ) + _1{1} , _1{2}) otherwise null

6. Click OK.

7. Click the Duration column and turn the ABC into "Duration" like below.

That is all that needs to be done.

Theo 🙂

[liamcol1]

I can't get the new column formula to work, are there any generic values that i need to replace to match my dataset 

[TheoC · Accepted solution]

@liamcol1 just make sure that your column is Text format before you run the Custom Colum:

The first column above is the ABC text based duration.

The next step, add Custom Column with the above code.

Third step, convert new column to duration.

[liamcol1]

Do these steps have to be done in the transform data power query editor? 

[TheoC]

Yeah, correct! In Power Query 🙂

[TheoC]

Hi @liamcol1 

Duration requires two points in time in order to be calculated. Currently, there is one point in time in your column. What are you using as the second point in time (e.g. 11:00am - 9:00am = 2 hours / Point 1 - Point 2 = Duration).

Thanks,

Theo