Compare two data sets, differ the duplicate values

[BusinessAnalyst]

Dear experts,

I have two tables which both contains column "Id" and column "purchase code". Values in both can be duplicates.

Table 1:

- column "Id": entire Id values 

- column "purchase code": 
Table 2:
- column "Id": is a sub data set from column Id in table 1
- column "purchase code": contains some of the purchase codes from table 1 but not all. It also contains other purchase codes which don't appear in table 1.

I'd like to create a column "Available in table 2" in table 1, that shows, for example;
Id             Purchase code                                 Available in table 2
1             2weer21566                                       Yes
1             uijbfdd1468                                        No

2             12ywoofne9                                       No

Thank you very much in advance for your insights.

Regards,
Cindy

[radpir · Accepted solution]

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

[MEhrenmueller · Accepted solution]

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

[radpir · Accepted solution]

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