Convert duration (text) to minutes

[maartjedutchy]

Dear PowerBI rockstars,

I have a duration (text format) which looks like 285d 4h 8m 10s (days hours minutes and seconds) which I want to convert to total minutes. However the duration could also be 1h 13m 15s (no whole days) or 1m 54s (no whole hours or days)

Examples:

281d 12h 13m 13s

1h 8m 38s

59m 50s

34s

I was thinking of doing some kind split column with a lookup/conditional column in PBI, if column contains "d' then take first 2 characters... But can't really figure it out in DAX. Does anybody have an idea how to convert this duration into minutes taking the various examples into account..?

Thanks,

Maartje

[Anonymous]

None of the above worked for me. Here is my code for text in the form: 1d23h12m1s (numbers arbitrary). I also have it returning hours but that's an easy switch.

Hours =

// Days

var indexOfD = SEARCH("d",Table[Column],1,1)

var d = VALUE(IF(indexofD>1, MID(Table[Column],1,indexOfD-1), "0"))

var strAfterD = IF(indexofD>1, MID(Table[Column], indexofD+1, LEN(Table[Column])-indexofD), Table[Column])

//Hours

var indexOfH = SEARCH("h",strAfterD,1,1)

var h = VALUE(IF(indexofH>1, MID(strAfterD,1,indexOfH-1), "0"))

var strAfterH = IF(indexofH>1, MID(strAfterD, indexofH+1, LEN(strAfterD)-indexofH), strAfterD)

//Minutes

var indexOfM = SEARCH("m",strAfterH,1,1)

var m = VALUE(IF(indexofM>1, MID(strAfterH,1,indexOfM-1), "0"))

var strAfterM = IF(indexofM>1, MID(strAfterH, indexofM+1, LEN(strAfterH)-indexofM), strAfterH)

//Seconds

var indexOfS = SEARCH("s",strAfterM,1,1)

var s = VALUE(IF(indexofS>1, MID(strAfterM,1,indexOfS-1), "0"))

return d*24+h+m/60+s/3600

[v-ljerr-msft · Accepted solution]

Hi @maartjedutchy,

Based on my test, the formula below should work in your scenario. 

Column = 
var indexOfD = SEARCH("d",Table1[Column1],1,1)
var indexOfH = SEARCH("h",Table1[Column1],1,1)
var indexOfM = SEARCH("m",Table1[Column1],1,1)
var indexOfS = SEARCH("s",Table1[Column1],1,1)
var d = VALUE(IF(indexOfD>1,MID(Table1[Column1],1,indexOfD-1),"0"))
var h = VALUE(IF(indexOfH-indexOfD=0,"0",IF(indexOfH-indexOfD>=4||indexOfH=3,MID(Table1[Column1],indexOfH-2,2),MID(Table1[Column1],indexOfH-1,1))))
var m = VALUE(IF(indexOfM-indexOfH=0,"0",IF(indexOfM-indexOfH>=4||indexOfM=3,MID(Table1[Column1],indexOfM-2,2),MID(Table1[Column1],indexOfM-1,1))))
var s = VALUE(IF(indexOfS-indexOfM=0,"0",IF(indexOfS-indexOfM>=4||indexOfS=3,MID(Table1[Column1],indexOfS-2,2),MID(Table1[Column1],indexOfS-1,1))))
return
d*3600+h*60+m+s/60

Regards

[jh186046]

I have similar data: duration formatted as text. When I applied the solution above, I get anerror saying the "MID" function has the wrong data type or an invalid value.

Any suggestions?