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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors