Case like statements - Compare two table columns

[Arial12]

Hi Experts,

I have two table with similar column that i want to compare and calculate a column.

something like this,

if tabl1.col1 = tabl2.col1 then "yes" else "NO"

any suggestion?

Thanks,

[v-yulgu-msft]

Hi @Arial12,

Based on my assumption, there existing a common field between two tables, which can determine the matching relationship between tables, right? And you want to compare the col1 in both tables, right? If so, please try below formula to create a calculated column.

New column =
IF (
    tab1[col1]
        = LOOKUPVALUE ( tab2[col1], tab2[matchingcolumn], tab1[matchingcolumn] ),
    "Yes",
    "No"
)

Best regards,

Yuliana Gu

[Anonymous]

Merge the tables into a new one in Power Query, then you can easily achieve what you want.

[Arial12]

can't merge them. both are very large tables

[Christann]

Try creating a relationship between the tables and then use a function like:

isequal = IF(Table[Column1]=RELATED(Table2[Column1]),"yes","no")

Hope this helps!

[Arial12]

Tables don't have a relationship.

[Christann]

As far as I know, you will have to create a relationship between the tables in order to compare between them. This may require creating an extra column on each of them to use as a link.

[dedelman_clng]

Then how do you know which values to compare?

[Arial12]

Each table has a column with a matching value with each other..

[Arial12]

Each table has a column with a matching value with each other..

[Christann]

Is there a reason that you cannot create a relationship based on those columns?

[Arial12]

yes, one is only Numeric and another one allows alph numeric values as well. 

[Anonymous]

Convert the numeric column to text and create a relationship between the two. If you need to run calculations off the column that then can use the VALUE function or create a duplicate column using a numeric data type.