Text Duration Conversion

Question

I keep finding uses for this crazy Text to Table pattern. This one converts text durations to numeric values. Didn't know this was a thing but has come up a couple times in a matter of days. I created a hacky version of this but this one is much, much better. Handles single and plural words, capitalization or not, any order, etc.
Duration = 
    VAR __Separator = " "
    VAR __SearchText = MAX('Table'[Text Duration])
    VAR __Len = LEN(__SearchText)
    VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,__Count,1),
                "__Word",
                    VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
                    VAR __Start =
                        SWITCH(TRUE(),
                            __Count = 1,1,
                            [Value] = 1,1,
                            FIND("|",__Text)+1
                        )
                    VAR __End = 
                        SWITCH(TRUE(),
                            __Count = 1,__Len,
                            [Value] = 1,FIND("|",__Text) - 1,
                            [Value] = __Count,__Len,
                            FIND(__Separator,__Text,__Start)-1
                        )
                    VAR __Word = MID(__Text,__Start,__End - __Start + 1)
                RETURN __Word
            ),
            "__Key",
                SWITCH(TRUE(),
                    SEARCH("day",[__Word],,0)&gt;0,"day",
                    SEARCH("hour",[__Word],,0)&gt;0,"hour",
                    SEARCH("minute",[__Word],,0)&gt;0,"minute",
                    SEARCH("second",[__Word],,0)&gt;0,"second",
                    BLANK()
                )
        )
    VAR __Days = MAXX(FILTER(__Table,[Value] = MAXX(FILTER(__Table,[__Key]="day"),[Value]) - 1),[__Word])+0
    VAR __Hours = MAXX(FILTER(__Table,[Value] = MAXX(FILTER(__Table,[__Key]="hour"),[Value]) - 1),[__Word])+0
    VAR __Minutes = MAXX(FILTER(__Table,[Value] = MAXX(FILTER(__Table,[__Key]="minute"),[Value]) - 1),[__Word])+0
    VAR __Seconds = MAXX(FILTER(__Table,[Value] = MAXX(FILTER(__Table,[__Key]="second"),[Value]) - 1),[__Word])+0
RETURN
    __Days * 1000000 + __Hours * 10000 + __Minutes * 100 + __Seconds
&nbsp;
eyJrIjoiZTk1MWVkZGItN2Y0Yy00ZmM4LTliMDYtYmM4MTFkMzc0NjhkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

greg_deckler · Answer

I revisited this solution after I remembered my Mythical DAX Index. A perhaps more elegant approach to Text to Table if you don't have | characters in your text is this:
    VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|")
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1,__Count,1),
            "__Word",PATHITEM(__Text,[Value],TEXT)
        )
If you did have | characters in your text, you would have to SUBSTITUTE them out and then SUBSTITUTE them back in for each word which could get a little messy.

nelvbautista · Answer

Hi&nbsp;Greg_Deckler&nbsp;,
&nbsp;
With having the same text duration format, what would the formula look like if the conversion needed for the duration would be in decimals (hhhh.mm) instead of your example above thats in dd:hh:mm:ss? Appreciate your updates.

greg_deckler · Answer

Nelvbautista&nbsp;So if you have 300 hours and 52 minutes do you want that as 300.52 or 300 + 52/60? The first one is:
&nbsp;
RETURN
    __Hours + __Minutes / 100

or

RETURN
    __Days * 24 + __Hours + __Minutes / 100
&nbsp;
Second is:
&nbsp;
RETURN
    __Hours + __Minutes / 60

or

RETURN
    __Days * 24 + __Hours + __Minutes / 60
&nbsp;