Forum Discussion
Convert Date/time/timezone field to duration.
Anonymous 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.
Community ChampionHi Anonymous
Apply the following:
- Convert column to Text
- 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"
- 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
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 null6. Click OK.
7. Click the Duration column and turn the ABC into "Duration" like below.
That is all that needs to be done.
Theo 🙂