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
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
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.
Do these steps have to be done in the transform data power query editor?
- TheoC
Yeah, correct! In Power Query 🙂