Unanswered

Removing Duplicates from Merged Tables in Power Query

(0) Share

Report

Posted on by gmelesse

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?

Categories:

Power Query

I have the same question (0)

All responses (2)

Answers (0)

Sort by

gmelesse 4 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No

gmelesse

4 on at

Like (0)

Report

Copy link

Link copied!

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