topic Re: How To Convert Text Duration (xx day XX hour XX minutes) to Decimal in DAX Commands and Tips

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

Nelvbautista — Mon, 23 Aug 2021 17:19:41 GMT

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...

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

amitchandak — Mon, 23 Aug 2021 17:48:04 GMT

Nelvbautista , Try a new column like

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

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

community_pinki — Mon, 23 Aug 2021 18:11:23 GMT

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...

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

Nelvbautista — Mon, 23 Aug 2021 19:20:16 GMT

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?

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

Greg_Deckler — Mon, 23 Aug 2021 20:58:54 GMT

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

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

Nelvbautista — Mon, 23 Aug 2021 21:24:49 GMT

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

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

Greg_Deckler — Tue, 24 Aug 2021 02:04:53 GMT

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.

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

Nelvbautista — Mon, 06 Sep 2021 13:55:57 GMT

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:

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

Greg_Deckler — Mon, 06 Sep 2021 15:27:12 GMT

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.

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

Nelvbautista — Mon, 06 Sep 2021 16:40:12 GMT

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