Unanswered

Removing Duplicates from Merged Tables in Power Query

Like (0) Share

Report

Posted on 23 Jun 2023 18:00:45 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?

All responses (2)

Answers (0)

gmelesse

4 on 29 Jun 2023 at 13:54:58

Like (0)

Report

Re: Removing Duplicates from Merged Tables in Power Query

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

gmelesse 4 on 29 Jun 2023 at 13:39:56

Like (0)

Report

Re: Removing Duplicates from Merged Tables in Power Query

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?

Community site session details

Removing Duplicates from Merged Tables in Power Query

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Leaderboard > Power Apps

Featured topics

Product updates

Community site session details

Removing Duplicates from Merged Tables in Power Query

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Power Apps

Featured topics

Product updates