Compare two tables having multiple text with delimiter

[Mechi]

Hello All,

 I need a help from the powerbi community members

I have two tables, Table 1 has multiple texts separated by comma in rows, Table 2 has texts in columns

Table 1                                                              

Sweets, Vegetables, Fruits, Meat

Table 2

Sweets

Vegetables

Fruits

Meat

I need to create a new column in Table 1 which should now compare the two tables, if the table 1 row (Sweets, Vegetables, Fruits, Meat) matches with all the text with a specific column in table 2 then  the results should be displayed as "OK"

Thanks for your help

Mechi 🔧                 

[Anonymous]

@Mechi please try concatenatex function

Column 2 = IF(CONCATENATEX(Table2,Table2[tbl2Col],", ",Table1[Column])=Table1[Column],"Ok")

[Mechi]

Hi Vimal,

 Thanks for your reply and solution

I was looking for a solution for the below example (snap), i haven't mentioned in detail in my earlier post

Thanks for the feedback

BR

Mechi 🔧

[Anonymous]

Column = IF(SUMX(Table2,FIND(LOWER(Table2[List 1]),LOWER(Table1[ITEMS]),,0))>0,"Ok","Not Ok")

@Mechi  Please create this column in Table1.

[Mechi]

It should compare the each word, but the formula finds & compares the first character and not the words

Mechi 🔧

[Anonymous]

Hi @Mechi  Thanks for poiting out that. Fixed that issue. Can you please try below one

Column = 
VAR countOfMatchingElement = DIVIDE(LEN(SUBSTITUTE(CONCATENATEX(Table2,CONTAINSSTRING(Table1[ITEMS],Table2[List 1])),"FALSE","")),4,0)
VAR countOfDelimiter = (LEN(Table1[ITEMS])-LEN(SUBSTITUTE(Table1[ITEMS],",","")))+1
RETURN IF((countOfDelimiter-countOfMatchingElement)<>0,"Not ok","Ok")

[Mechi]

Hi Vimal,

 Thanks for your efforts 👍

 The results are almost close, except the one with exact string match for the last one

  

 Mechi 🔧

[Anonymous]

Hi @Nathaniel_C , @Mariusz  Sorry for tagging.
Any input how to resolve this problem

List 1

Sweets

Fruits

Furnitures

Clothes

Vegetables

Meat

Flowers

Toys

Forest

ITEMS

Sweets, Vegetables, Fruits, Meat

Flowers, Toys, Drinks

Furnitures, Clothes

Forests

[Mechi · Accepted solution]

Hello Vimal,

 Thanks for your effors and support in sharing the knowledge and solutions 😊

Finally we had arrived to a solution using power query, Posted the solution in Power Query Forum, Link to solution

BR

Mechi 🔧