Identify duplicates

[CTan42]

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.

[v-yuezhe-msft · Accepted solution]

@CTan42,

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


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)


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

Regards,
Lydia