Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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.
@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
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.