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.
This one falls solidly into the "there are better ways to do this" camp. Essentially replace multiple values in a string of text.
Comes from this thread. This is basically Text to Table with a different twist.
Measure =
VAR __Separator = "*"
VAR __SearchText = MAX('Table2'[Column1])
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
),
"__Replaced",LOOKUPVALUE('Table'[Description],'Table'[Code],[__Word])
)
RETURN
CONCATENATEX(__Table,[__Replaced],__Separator,[Value])
eyJrIjoiN2ZlZDc0MzctMDg5NC00NzM1LTk1OTMtNDdlOTkxZmNhMjg3IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9&pageName=ReportSection
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.