multiple values in a column comparison

[sanjanarama]

I have two tables as below with common attribute as ID.

I wanted to compare names column from two tables if they match or not. Data in the columns are comma seperated values and can be present anywhere, 

ID	parent Names
1	Apple, Orange
2	Lemon,grapes, Fruit
3	Hello, Fridge, Door
4	balcony
6	tree, time

ID	Family Names
2	Fruit, Lemon, Grapes
3	fridge, hello, Door
6	tree

Outcome :

ID	Match result
1	no match
2	true match
3	true match
4	no match
6	partial match

[v-henryk-mstf · Accepted solution]

Hi @sanjanarama ,

For this kind of output, judging by looping through the string may not be possible in powerbi. However, you can use a function like find to find out if the corresponding field exists in the list.

1 = 
IFERROR ( FIND 
    ( "Apple", 
        Table1[parent Names] ), 
            BLANK () 
    )
    && 
    IFERROR ( FIND 
    ( "Orange", 
        Table1[parent Names] ),
             BLANK () )

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HenriqueReis]

Hi!

If you want three types of results, first of all, you should separate the columns in just one item. 

Since the table has comma seperated values, you could open Power Query and separate by delimiter.

Then will be easier to do what you want.

Regards!