Contains check of two Columns in two tables

[DONB87]

Hii All

I have a probelm. I have to tables with two columns.

Table 1

Personname: Costumername  1 ( Smith)
                      Costumername 2 ( Green)

                     Costumername 3 ( Summer)

Table 2
PERNAME: Costumername 1 ( Jerry  Paul Clarkson)
                  Costumername 2 (Jenny Summer Smith)
                  Costumername 3 (Mr Clark  Paul Jackson)

What i need is if there is a contain make = 1 and if not then 0.

I have tried a lot of functions but nothing helped. In other words i need a contains function that are checking the table 1 colum with the table 2 column.

Thanks and best wishes

Donb87

[tamerj1 · Accepted solution]

@DONB87 
Please do Trim and Clean to Table1 column then use the following M code and refer to attached sample file for more details.

= Table.AddColumn(#"Cleaned Text","Custom", each if Text.Contains(Text.Upper(Text.Combine(#"Table2"[PERNAME],",")),Text.Upper([Personname])) then 1 else 0)

[DONB87]

Hii @tamerj1 

Unfortunatelly im getting only 0, because it compares the entire cell and not contains 😞 Maybe other ideas, that can help my to resolve my probelm.

Best wishes

Donb87

[tamerj1]

@DONB87 

Actually no. Text.Contains compares contains not whole text.
Again, power query is case sensitive.

[DONB87]

Hii @tamerj1 

What i need is this:

I have tried your code but im getting only 0.

Thanks a lot for your help!

BEst

Donb87

[tamerj1]

@DONB87 
Power Query is case sensetive. First capitalize both columns then add the custom column.

[tamerj1]

Hi @DONB87 
Please refr to attached sample file with the solution.

Check = 
INT ( 
    NOT ISEMPTY ( 
        FILTER ( 
            Table2,
            CONTAINSSTRING ( Table2[PERNAME], Table1[Personname]  )
        )
    ) 
)

[DONB87]

HI @tamerj1 
Can i implement this function in a custom column?

Best wishes

Donb87

[tamerj1]

@DONB87 

= Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains(Text.Combine(#"Table2"[PERNAME],","),[Personname]) then 1 else 0)