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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
sparvez
Helper I
Helper I

Return matched values

Dear altruists,

 

I have a column with many values which can be increased, such as;

Column 1

Apple

Pear

Kaki

Avocado

etc...

 

These values I want to find in another column, for multiple values add a sign , such as "/". Ther power query I am looking for is Result column

Column 2--------Column Result

Apple is red--------Apple

Orange, Apple is orange--------Orange/Apple

Avocado, kaki goes bad--------Avocado/kaki

Kaki is yellow--------Kaki

 

Can any one help pls?

 

regards

1 ACCEPTED SOLUTION
Manoj_Nair
Solution Supplier
Solution Supplier

I have taken your sample set and produced the below output

Manoj_Nair_0-1670008025344.png

 

I created TBL1 which has Columns 1 and TBL2 which has the Column 2 containing text within which Column 1 is to searched.

The Custom column was created using the below Transformation step

Manoj_Nair_1-1670008025346.png

 

If the solution matches your requirement, please let me know I will upload the PBIX file

View solution in original post

4 REPLIES 4
sparvez
Helper I
Helper I

Hi Manjo,  it worked , thanks a lot. Have a good day.

Manoj_Nair
Solution Supplier
Solution Supplier

I have taken your sample set and produced the below output

Manoj_Nair_0-1670008025344.png

 

I created TBL1 which has Columns 1 and TBL2 which has the Column 2 containing text within which Column 1 is to searched.

The Custom column was created using the below Transformation step

Manoj_Nair_1-1670008025346.png

 

If the solution matches your requirement, please let me know I will upload the PBIX file

Hi Manoj,

Sorry, its not working, i add here the formula. Can u pls have a look?

 

= Table.AddColumn(#"Changed Type", "Custom", each List.Accumulate(Table.ToList(#"Tbl1","",(state,current)=>if Text.Contains([Column 2], current)
then current&"/"&state
else state

)))

@sparvez Please take look at the attached pbix file

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors