Forum Discussion
multiple values in a column comparison
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 |
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.
2 Replies
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!
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.
