Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
coejnot
Frequent Visitor

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:

CodeDescription
ABlue
BGreen
CRed
D

Yellow


I would like to create a column where the result picks the description from the table. 

The result should be like this:

Blue*Green*Red*Blue*Red

 

I would really appreciate if you could help me with a dax for this.

 

Thanks!

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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})),"*")
    )

 

 

Fowmy_0-1628601477861.png

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
Fowmy
Super User
Super User

@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})),"*")
    )

 

 

Fowmy_0-1628601477861.png

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

coejnot
Frequent Visitor

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors