Forum Discussion
Solved
Substiute multiple values in a text from another table
Hi, I have this string in a column: A*B*C*A*C Then I have this table: Code Description A Blue B Green C Red D Yellow I would like to create a column where the r...
coejnot
You can add a custom column in Power Query and achieve the same. File is attached
Text.Combine(
List.Transform(
Text.Split([Column1],"*"),(a)=> Text.Replace(a,a, Table.SelectRows( Table2, (i)=> i[Code] = a)[Description]{0})),"*")
)
This worked really well, thank you!
coejnot I realize that this is already solved and Fowmy solution is the best one. However, I couldn't help creating a DAX solution for this. The solution is based on my Text to Table measure:
Column = VAR __Separator = "*" VAR __SearchText = [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])Hi, Greg_Deckler
I tried this out too, and it also works as I expected.
Interesting to learn even more about how to create things in DAX.
Thank you!
