Compare if value exists on another table based on a column id

[Dee]

Hello,

I happen to have two tables where am trying to compare whether the values are the same based on the unique id.

example:

Table 1

id product value alt1 alt2

aaa oranges 50  60 70

bbb bananas 70 90 80

ccc grapes 10 20 30

Table 2

id product value alt1 alt2

aaa oranges 50  60 70

bbb pears 70 80 90

I need help on how to compare the info as similar based on the id and return:

output would look something like this:

id value

aaa exists

bbb different

ccc missing

any help would be highly appreciated.

TIA

[v-lid-msft]

Hi @Dee ,

We can create a  calculate column to meet your requirement.

values =

VAR lookup_ =
    LOOKUPVALUE ( Table2[product], Table2[id], Table1[id] )
RETURN
    IF (
        ISBLANK ( lookup_ ),
        "missing",
        IF ( [product] = lookup_, "exists", "different" )
)

Or

Value_2 =
VAR i = [id]
VAR p = [product]
VAR n1 =
    CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[id] = i )
VAR n2 =
    CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[id] = i, 'Table2'[product] = p )
RETURN
    IF ( n1 = 0, "Missing", IF ( n2 = 1, "exists", "different" ) )

BTW, pbix as attached.

Best regards,

[Dee]

Hi @v-lid-msft 

Thank you so much for this detailed explanation.

However, how do I tweak the second formula, that compares multiple columns in a row.

eg in the example based on The id, I want to ensure also the product, value, alt1, and alt2 are also evaluated, so as to return the formula and not just the product.

Ps. These items all have to appear in the same row of the IDs, for the result to be "exists", if one is missing "Difference" if the Id doesn't appear in both tables "missing" Thank you once more for your help.

[Greg_Deckler]

Sounds like you need LOOKUPVALUE or a combination of MAXX(FILTER(...)...)

[Anonymous]

Can this same thing be accomplished in Power Query?  ...to see if a record exists in one table and then perform calculation if it does.