Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

Compare two data sets, differ the duplicate values

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

2 ACCEPTED SOLUTIONS
radpir
Resolver II
Resolver II

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

 

 Screenshot1.jpg

2. Add a custom column with the following formula:

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

Screenshot2.jpg

3. Remove the NewColumn column.

 

Hopefully this works for you.

 

Regards,

radpir

View solution in original post

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

Markus Ehrenmueller-Jensen
@MEhrenmueller

View solution in original post

2 REPLIES 2

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

Markus Ehrenmueller-Jensen
@MEhrenmueller
radpir
Resolver II
Resolver II

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

 

 Screenshot1.jpg

2. Add a custom column with the following formula:

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

Screenshot2.jpg

3. Remove the NewColumn column.

 

Hopefully this works for you.

 

Regards,

radpir

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors