Forum Discussion

sparvez's avatar
sparvez
Helper I
3 years ago
Solved

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 val...
Need Help
  • Manoj_Nair's avatar
    Manoj_Nair
    3 years ago

    I have taken your sample set and produced the below output

     

    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

     

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

Manoj_Nair's avatar
Manoj_Nair
Solution Supplier
3 years ago

I have taken your sample set and produced the below output

 

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

 

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

sparvez's avatar
sparvez
Helper I
3 years ago

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

)))