Forum Discussion
Removing Duplicates from Merged Tables in Power Query
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
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 |