Forum Discussion

Frequent Visitor

5 years ago

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...

Fowmy
5 years ago
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})),"*") )
Coejnot - Substiute multiple values in a text from another table.pbix17 KB

Greg_Deckler

Community Champion

5 years ago

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])