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

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors