topic Re: Text Duration Conversion in Quick Measures Gallery

Text Duration Conversion

Greg_Deckler — Tue, 24 Aug 2021 23:52:32 GMT

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)>0,"day", SEARCH("hour",[__Word],,0)>0,"hour", SEARCH("minute",[__Word],,0)>0,"minute", SEARCH("second",[__Word],,0)>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

eyJrIjoiZTk1MWVkZGItN2Y0Yy00ZmM4LTliMDYtYmM4MTFkMzc0NjhkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Text Duration Conversion

Greg_Deckler — Mon, 30 Aug 2021 13:52:53 GMT

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.

Re: Text Duration Conversion

Nelvbautista — Wed, 08 Sep 2021 12:39:17 GMT

Hi Greg_Deckler ,

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.

Re: Text Duration Conversion

Greg_Deckler — Wed, 08 Sep 2021 13:05:14 GMT

Nelvbautista So if you have 300 hours and 52 minutes do you want that as 300.52 or 300 + 52/60? The first one is:

RETURN __Hours + __Minutes / 100 or RETURN __Days * 24 + __Hours + __Minutes / 100

Second is:

RETURN __Hours + __Minutes / 60 or RETURN __Days * 24 + __Hours + __Minutes / 60