Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
AAAbbbnewbie
Frequent Visitor

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

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

 

AAAbbbnewbie_0-1689673058678.png

 

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee

View solution in original post

4 REPLIES 4
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee

Thank you so much ! it works perfectly for me! 

mlsx4
Super User
Super User

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]

 

 

Thanks for sharing 🙂 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors