Forum Discussion
REMOVING DUPLICATES AND MERGING QUERIES
on the steps, after sorting data, a new step will added into your query steps which its formula starts with Table.Sort(....), befor removing the duplicteas, revise this formula by addin Table.Buffer befor it and rewrite the formula as
Table.Buffer(Table.Sort(....))then remove duplicat. your peoblem become solved
Super UserPlease share your M code and examples of your data.
I am unable to provide the sample data due to privacy issue
However, i have created a replica as shown below for QUERY 1
| Ac No | Customer Name | PIN | SD | ED | Created |
| 1 | A | 0001 | 31/10/2024 | 27/02/2027 | 31/10/2024 |
| 2 | B | 0002 | 07/11/2024 | 06/11/2027 | 31/10/2024 |
| 3 | C | 0003 | 30/10/2024 | 31/12/9998 | 31/10/2024 |
| 4 | D | 0004 | 01/01/2025 | 31/12/2025 | 31/10/2024 |
| 4 | D | 0004 | 01/09/2024 | 31/12/9998 | 21/10/2024 |
| 5 | E | 0005 | 02/07/2025 | 01/07/2028 | 31/10/2024 |
| 5 | E | 0005 | 08/08/2024 | 22/09/2024 | 22/10/2024 |
| 6 | F | 0006 | 01/10/2024 | 31/12/9998 | 31/10/2024 |
| 6 | F | 0006 | 14/08/2024 | 30/10/2024 | 26/10/2024 |
The data is first sorted by created to show newest to oldest
and duplucate is removed for which it returns the below
| Ac No | Customer Name | PIN | SD | ED | Created |
| 1 | A | 0001 | 31/10/2024 | 27/02/2027 | 31/10/2024 |
| 2 | B | 0002 | 07/11/2024 | 06/11/2027 | 31/10/2024 |
| 3 | C | 0003 | 30/10/2024 | 31/12/9998 | 31/10/2024 |
| 4 | D | 0004 | 01/01/2025 | 31/12/2025 | 31/10/2024 |
| 5 | E | 0005 | 02/07/2025 | 01/07/2028 | 31/10/2024 |
| 6 | F | 0006 | 01/10/2024 | 31/12/9998 | 31/10/2024 |
This is then meant to be merge with another query 2 using the Pin, but ended up returning the deleted values after merging
The Steps code :
Sorting : = Table.Sort(#"Changed Type1",{{"Created", Order.Descending}})
Removing Duplicate: = Table.Distinct(#"Sorted Rows1", {"PIN"})
Merging on query 2: = Table.NestedJoin(#"Added Custom", {"PIN1 "}, #"Query1", {"PIN"}, "Query1", JoinKind.LeftOuter)