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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vkandala
Frequent Visitor

convert run_duration (DD: HH: MM: SS) column to Seconds.

Hello,

 

I am trying to convert run_duration (DD: HH: MM: SS)  column to Seconds. 

For example, if Run duration is 1 Day: 1 hour: 1 minute: 1 second I need to convert it to 90061 seconds

 

86400 + 3600 + 60 + 1 = 90061.

 

 

Thanks

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Seconds = VAR DurationString = SUBSTITUTE(Durations1[Duration], ":", "|")
VAR Days = PATHITEM(DurationString,1)
VAR Hours = PATHITEM(DurationString,2)
VAR Minutes = PATHITEM(DurationString,3)
VAR Seconds = PATHITEM(DurationString,4)
RETURN Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

Seconds = VAR DurationString = SUBSTITUTE(Durations1[Duration], ":", "|")
VAR Days = PATHITEM(DurationString,1)
VAR Hours = PATHITEM(DurationString,2)
VAR Minutes = PATHITEM(DurationString,3)
VAR Seconds = PATHITEM(DurationString,4)
RETURN Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...


@smoupre wrote:
Seconds = VAR DurationString = SUBSTITUTE(Durations1[Duration], ":", "|")
VAR Days = PATHITEM(DurationString,1)
VAR Hours = PATHITEM(DurationString,2)
VAR Minutes = PATHITEM(DurationString,3)
VAR Seconds = PATHITEM(DurationString,4)
RETURN Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds

@Greg_Deckler

Wow, it is really tricky to use PATHITEM to get the substrings. Awesome work Smiley LOL.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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