How to convert the text value "50:23:06" to seconds in Power Query,

[AAAbbbnewbie]

Hi, how should i convert below time value exceed 24hours to seconds in PBI? Please help~~~

[ppm1 · Accepted solution]

Add a custom column in the query editor and put this expression in the pop-up box.

let 
hms = List.Transform(Text.Split([Other Work], ":"), each Number.From(_)),
result = hms{0}*60*60 + hms{1}*60 + hms{2}
in 
result

Pat

[AAAbbbnewbie]

Thank you so much ! it works perfectly for me! 

[mlsx4]

Hi @AAAbbbnewbie :

- First, in Power Query, separate the time by character : (You will get three columns: hours,minutes and seconds).

-Then, create a custom column: 

Time= [Other work Hours]*3600+[Other work Minutes]*60+[Other work Seconds]

[AAAbbbnewbie]

Thanks for sharing 🙂