How To Convert Text Duration (xx day XX hour XX minutes) to Decimal

[Nelvbautista]

Hi, 

I need help with converting duration values in text (xx day XX hour XX minutes) to decimals (hhhh.mm) using DAX. 

Appreciate any help guys...

[Greg_Deckler · Accepted solution]

@Nelvbautista If you truly want a DAX solution, try this:

Column = 
    VAR __Duration = [Ticket Duration]
    VAR __DayLocation = SEARCH("day",__Duration,,0)
    VAR __HourLocation = SEARCH("hour",__Duration,,0)
    VAR __MinuteLocation = SEARCH("minute",__Duration,,0)
    VAR __Days = IF(__DayLocation,VALUE(LEFT(__Duration,__DayLocation - 1)),0)
    VAR __DaySeparator = IF(__DayLocation<>0,SEARCH(" ",__Duration,__DayLocation,0),1)
    VAR __HourSeparator = 
        SWITCH(TRUE(),
            __DayLocation<>0,SEARCH(" ",__Duration,__DaySeparator+1,1),
            __HourLocation=0,1,
            SEARCH(" ",__Duration)
        )
    VAR __Hours = IF(__HourLocation=0,0,VALUE(MID(__Duration,__DaySeparator,__HourSeparator - __DaySeparator + 1)))
    VAR __MinuteSeparator = IF(__HourSeparator=1,1,SEARCH(" ",__Duration,__HourSeparator+1,0))
    VAR __LastSeparator = SEARCH(" ",__Duration,__MinuteSeparator+1,0)
    VAR __Minutes = VALUE(MID(__Duration,__MinuteSeparator,__LastSeparator - __MinuteSeparator + 1))
RETURN
    __Days * 24 + __Hours + __Minutes/100

[Nelvbautista]

@Greg_Deckler appreciate your help on this a couple of weeks back. I'm now getting an error, "An argument of function 'MID' has the wrong data type or has an invalid value" when I expended the table to include historical data.

Below is the updated duration column (blanks are 0 duration). Appreciate any help you can provide:

[Greg_Deckler]

@Nelvbautista I made an improved version here: Text Duration Conversion - Microsoft Power BI Community

Also, in the comments, note that there is an alternate (better) way to do the text 2 table bit.

[Nelvbautista]

Hi @Greg_Deckler,

As expected, really appreciate the speedy response. I tried (copy-pasting) the updated formula and only returned 900 on all rows.

I was also prompetd to update to the latest Power BI Desktop version when trying to open your pbix attachment. My current version is "2.93.981.0 64-bit (May 2021)".  I might need to wait for our IT for this update first. Again, appreciate the help.

Nel

[Nelvbautista]

@Greg_Deckler appreciate the help, the formula works, thank you very much.

[Greg_Deckler]

@Nelvbautista Glad to hear it because that one took a bit of time! You could likely convert that formula to Power Query if you were so inclined using the function equivalents for SEARCH, LEFT, MID, etc. in Power Query.

[Nelvbautista]

@amitchandak @community_pinki appreciate the assistance and time in looking into this, but would there be an option to have this as a formula because this will be a growing dataset wherein new rows will be continously added?

[community_pinki]

Hey @Nelvbautista ,

Follow below steps it will work :-
Step 1 : Go to the Power Query Editor --> Click on the Duration Column and apply some transformation like "lowercase" and "trim"
Step 2 : To extract the day from column go to the Add Column --> Extract --> Click on text before delimeter --> text before delimeter popup will open
--> Specify Delimeter as "day" and in the advanced option --> select "From the end of the input" from Scan for delimter dropdown.
It will create new column rename it with Day.
Step 3: Add Column --> Extract --> now select for "Text between delimeter"
It will open the popup for Text between delimeter --> Spcify Start delimter as press space button and then type hour
--> Spcify End delimter as press space button
--> In the Advanced option select "From the end of the input" from Scan for start delimeter dropdown.
--> select "From the start delimeter, toward the start of the input"
--> Click OK.
Step 4: Follow the 3rd step for minutes and seconds
Step 5:Rename all the column as Day, Hour, Minutes, seconds respectively.
Step 6: Copy all four columns Goto the Tranform-->Replace values --> Values to Find--> leave it blank -->Specify "Replace with" as 0--> Click OK.
Step 7 : Copy all fourcolumns Again Goto the transform --> Merge Column -->Seperator: colon-->Specify column Name-->Click OK
Step 8: Right click on the column and change the data type as "Duration"

Thanks...

[amitchandak]

@Nelvbautista , Try a new column like

Number.FromText (Text.Combine(List.RemoveItems(Text.Split([ticket duration], " "), {"hour", "minutes", "day"} ),"."))