Compare two data sets, differ the duplicate values

Hi Cindy,

Basically you want to list all the records in Table1 and see if there are any matching records in Table2 where the match is on ID and Purchase code.

If that's right here's the solution:

1. Merge (Left Join) both tables on ID and Purchase code

2. Add a custom column with the following formula:

= if Table.IsEmpty([NewColumn]) then "No" else "Yes"

3. Remove the NewColumn column.

Hopefully this works for you.

Regards,

radpir

0. Import both tables separately into Power BI

1. Add to both tables (with Power Query) a new column "MyKey" and formula "each Text.From([Id]) & "%" & [purchase code]" (feel free to use a different character than "%" depending on the fact which characters would be allowed in purchase code - you must use a character which is not allowed in purchase code).

2. Add to table 2 a column with name "AvailableInTable2" and constant "yes".

3. Change relationship between both tables to join by column "MyKey".

4. Add a new calculated column to table 1 with name "Available in table 2" with formula "IF(RELATED('Table 2'[AvailableInTable2])="yes", "yes", "no")"

I made up an example, which you can find here:

https://1drv.ms/f/s!AnCGtBscBWuhbUwX0l_TEvMPVU4