topic Re: Contains check of two Columns in two tables in DAX Commands and Tips

Contains check of two Columns in two tables

DONB87 — Thu, 09 Feb 2023 09:43:46 GMT

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

Re: Contains check of two Columns in two tables

tamerj1 — Thu, 09 Feb 2023 11:43:20 GMT

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

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

Re: Contains check of two Columns in two tables

DONB87 — Thu, 09 Feb 2023 12:47:07 GMT

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

Best wishes

Donb87

Re: Contains check of two Columns in two tables

tamerj1 — Thu, 09 Feb 2023 13:31:15 GMT

DONB87

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

Re: Contains check of two Columns in two tables

DONB87 — Thu, 09 Feb 2023 13:55:29 GMT

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

Re: Contains check of two Columns in two tables

tamerj1 — Thu, 09 Feb 2023 14:33:13 GMT

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

Re: Contains check of two Columns in two tables

DONB87 — Fri, 10 Feb 2023 06:34:36 GMT

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

Re: Contains check of two Columns in two tables

tamerj1 — Fri, 10 Feb 2023 07:59:20 GMT

DONB87

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

Re: Contains check of two Columns in two tables

tamerj1 — Fri, 10 Feb 2023 11:30:41 GMT

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)