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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.