Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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.
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
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?