Removing Duplicates from Merged Tables in Power Query

[Syndicate_Admin]

I am doing a reconciliation from two different data set to find variances but when I do a full outer join between the tables I get duplicates if one of the tables are more than one row for the merge fields. There no unique identifier so I'm merging them using Resource, Date and hours

Table A
Resource	Invoice ID	Date	Hours
John Doe	12345	8/28/2023	40

Table B
Resource	Invoice ID	Date	Hours
John Doe	45678	8/28/2023	40
John Doe	98765	8/28/2023	-40
John Doe	76543	8/28/2023	40

Here's what I'm getting: 

Table A Resource	Table A Invoice ID	Table A Date	Table A Hours	Table B Resource	Table B Invoice ID	Table B Date	Table B Hours
John Doe	12345	8/28/2023	40	John Doe	45678	8/28/2023	40
John Doe	12345	8/28/2023	40	John Doe	98765	8/28/2023	-40
John Doe	12345	8/28/2023	40	John Doe	76543	8/28/2023	40

Here's what I want to see:

Table A Resource	Table A Invoice ID	Table A Date	Table A Hours	Table B Resource	Table B Invoice ID	Table B Date	Table B Hours
John Doe	12345	8/28/2023	40	John Doe	45678	8/28/2023	40
			40	John Doe	98765	8/28/2023	-40
			40	John Doe	76543	8/28/2023	40

Can someone help me, please?

[Syndicate_Admin]

This only works for the first record. If there's a same information for another resource, then I'm getting all records from Table A and Table B without any match. For example: If my table are:

Table A
Resource	Invoice ID	Date	Hours	Index
John Doe	12345	8/28/2023	40	0
John Doe	22222	10/10/2023	32	1

Table B
Resource	Invoice ID	Date	Hours	Index
John Doe	45678	8/28/2023	40	0
John Doe	98765	8/28/2023	-40	1
John Doe	76543	8/28/2023	40	2
John Doe	33333	10/10/2023	32	3
John Doe	44444	10/10/2023	-32	4
John Doe	55555	10/10/2023	32	5

I'm getting:

Resource	TableA.Invoice ID	TableA.Date	TableA.Hours	TableB.Invoice ID	TableB.Date	TableB.Hours
John Doe	12345	8/28/2023	40	45678	8/28/2023	40
null	null	null	null	98765	8/28/2023	-40
null	null	null	null	76543	8/28/2023	40
null	null	null	null	33333	10/10/2023	32
null	null	null	null	44444	10/10/2023	-32
null	null	null	null	55555	10/10/2023	32
John Doe	22222	10/10/2023	32	null	null	null

What I want to get is:

Resource	TableA.Invoice ID	TableA.Date	TableA.Hours	TableB.Invoice ID	TableB.Date	TableB.Hours
John Doe	12345	8/28/2023	40	45678	8/28/2023	40
null	null	null	null	98765	8/28/2023	-40
null	null	null	null	76543	8/28/2023	40
John Doe	22222	10/10/2023	32	33333	10/10/2023	32
null	null	null	null	44444	10/10/2023	-32
null	null	null	null	55555	10/10/2023	32

[Syndicate_Admin]

If a data is only in Table B, I would want the merged record in Table A to show null. How can I compare a null value to previous row only for data that are in both tables?

[Anonymous · Accepted solution]

Hi @Syndicate_Admin ,

Please try:

1. Add an index column to both tables;

2.  Merge Queries --> Merge Queries as New --> Select Table A as the first table and Table B as the second table --> Select the Resource, Date, Hours, and Index columns in both tables --> Choose the Full Outer join type and click OK

3. Expand the new merged column to include the desired columns from Table B.

4. Rename the columns as needed.

Result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Martin_D]

You cannot produce the result you want from a single merge step. If your requirements demand these empty cells then you need to clean up them afterwards. E.g., you could compare the first 3 columns with the previous row as described here, check whether a Table A row is a duplicate of the previous row, and add custom columns in your first-and-blank-rows format. Then finally remove the columns from table A from the merge result that you don't want to have.