cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CTan42 Helper II
Helper II

Identify duplicates

Hi all, here is my scenario. It is quite complicated. I cant show you the real table, however i made another similar tables.

Table 1 (Receive Item):

ReceiveID          Item          Payment

      1                 Car A             $50
      2                 Car B             $50
      3                 Car C             $50
      4                 Car D             $50
      5                 Car E              $50

      6                 Car F              $50

etc...

 

Table 2 (Invoice Item):

ReceiveID          Item          Payment

      1                 Car A             $50
      2                 Car B             $50
      3                 Car C             $50
      4                 Car D             $50
      5                 Car E              $50

      6                 Car F              $50

etc...

 

So my question is i would like to find out what are the data from table 1 are appearing in table 2. I'm sure that there is a lot of ways to do it. I had failed to link the tables together (i created another new table for ID as a middle key). I tried the IF function however it doesnt support cross table. I need help. Thanks.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: Identify duplicates

@CTan42,

Merge the two tables in Query Editor as shown in the following screenshots.
1.JPG2.JPG

Then create a column in the merged table.

Duplicate = IF(Merge1[RecevieID]=Merge1[Table2.RecevieID]&&Merge1[Item]=Merge1[Table2.Item]&&Merge1[Payment]=Merge1[Table2.Payment],1,0)
3.JPG

If the above steps don't help, please post more sample data and post your expected result here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Microsoft
Microsoft

Re: Identify duplicates

@CTan42,

Merge the two tables in Query Editor as shown in the following screenshots.
1.JPG2.JPG

Then create a column in the merged table.

Duplicate = IF(Merge1[RecevieID]=Merge1[Table2.RecevieID]&&Merge1[Item]=Merge1[Table2.Item]&&Merge1[Payment]=Merge1[Table2.Payment],1,0)
3.JPG

If the above steps don't help, please post more sample data and post your expected result here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors