Forum Discussion
Removing Duplicates from Merged Tables in Power Query
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?
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 TeamIf 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
4 Replies
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.
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 TeamIf 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
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?
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
