Difficult comparison of two tables

Help
, , ,
1

I have two data tables.
The first is called “old” and the second is “new”.

  1. I have to merge the tables by removing duplicates if any, and in the left lines add the status “ok”. the previous lines must remain and the new entries deleted.

  2. If there were invoices in the “old” table and they are not in the “new” table, the status in the merged table should be “old”

  3. If there are invoices in the “new” table that are not in the “old” table, the status in the merged table should be “new”

I am enclosing an “old” and “new” table and a table how it should look like after merging.

Please help
Unfortunately, I can’t attach anything because the message "

“Sorry, new users can not upload attachments.”

2

Hi @aleksandra.kondraciuk

Welcome to forum

Can u share a screenshot of it

3

Thanks for your quick response :smiley:
I’m trying but I have a message:
Sorry, new users can only put one embedded media item in a post.
Read more here on how to easily remove this restriction.

4

table old:

|date of registration|Data|InvoiceNumber|Company|Value|
|2020-06-24|2020-03-01|1/2020|a|567|
|2020-06-24|2020-03-14|abc/3/2020|b|4532|
|2020-06-24|2020-04-13|x/04/05/2020|c|7900|
|2020-06-24|2020-06-16|09/06/f/2020|d|4500|

table new:

|date of registration|Data|InvoiceNumber|Company|Value|
|2020-06-25|2020-03-14|abc/3/2020|b|4532|
|2020-06-25|2020-06-16|09/06/f/2020|d|4500|
|2020-06-25|2020-06-18|4/x/2020|x|3500|
|2020-06-25|2020-06-25|5/2020|y|4000|

Result
|date of registration|Data|InvoiceNumber|Company|Value|Status|
|2020-06-24|2020-03-01|1/2020|a|567|old|
|2020-06-24|2020-03-14|abc/3/2020|b|4532|ok|
|2020-06-24|2020-04-13|x/04/05/2020|c|7900|old|
|2020-06-24|2020-06-16|09/06/f/2020|d|4500|ok|
|2020-06-25|2020-03-14|abc/3/2020|b|4532|remove|
|2020-06-25|2020-06-16|09/06/f/2020|d|4500|remove|
|2020-06-25|2020-06-18|4/x/2020|x|3500|new|
|2020-06-25|2020-06-25|5/2020|y|4000|new|

5

then sample real tables are very large but number of columns, name and order are the same.
The number of rows in the table varies.

6

I solved the problem with power query.