Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
OK, this is another variation on Text to Table - Microsoft Power BI Community. Basically, capitalize every word. Again, there are better ways to do this but this is one way of doing it in DAX. If nothing else, this and The Replacements - Microsoft Power BI Community demonstrate the flexibility and utility of the Text to Table approach.
Caps =
VAR __Separator = "_"
VAR __SearchText = MAX('Capitalize'[Text])
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
),
"__Replace", UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1)
)
RETURN
CONCATENATEX(__Table,[__Replace]," ",[Value])
To handle one letter words, use this in place of the "__Replace" line of code:
"__Replace",
VAR __Rest = LEN([__Word])-1
RETURN
IF(__Rest<=1,UPPER(LEFT([__Word],1)),UPPER(LEFT([__Word],1)) & RIGHT([__Word],LEN([__Word])-1))
eyJrIjoiNWQ1YmQ0MjctNDcxZC00MmQxLTliOGMtYjdlNDZmYjczYmJiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
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.