Forum Discussion
REMOVING DUPLICATES AND MERGING QUERIES
Hi All,
I am hoping i could get some help,
I have two set of queries on Power Query and for query 1 ( I have duplicates data due to entries on different date), i have sorted the date in descending order cause i would like to keep the latest date, i removed duplicates (using customer number ) after sorting and then proceed to merge query 1 with query 2 using a uniques value (customer number). To my amusement , the merge query is returning values in query 1 that was meant to have removed through duplicates down
So say Customer A has Unique number 5 and has dates pn the 31/10/2024 and 21/10/2024 when i remove the duplicates , i could only see 31/10/2024 values (which is fine) but upon merging with query 2 , i am seeing values of 21/10/2024 been returned
How can i resolve this please
Thanks
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
14 Replies
- PwerQueryKees
Super User
Sorry, can't help you then...
- Omid_Motamedise
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
- PwerQueryKees
Hi Omid_Motamedise , I see you introduce the Table.Buffer in the solution. Can you explain why that works? In my mind it is only a performance function and does nothing functionally.
- Omid_Motamedise
Hi, it is true that Table.Buffer is mostely for performance but in this case, it can be used. Power query do not run the step, after each other in the order you create and it try to find the best order. so consider the below two steps
S0=Source,S1=Table.Sort(S0,....),
S2=Table.Distinct(S1,....)
in thiss case, it seems that S1 is neglected and the duplicate rows are removed befor sorting, adding Table.Buffer in this step, forced power query to sort the rows and remove duplicate.
this problem can be solved in another way by combining the S1 and S2 and rewrite it asS0=Source,
Srev=Table.Distinct(Table.Sort(S0,....),....)
Thank you Omid_Motamedise , the buffer function perfectly solves the problem
- Omid_Motamedise
You are welcome
- PwerQueryKees
Please 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 1Ac 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 belowAc 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)
- PwerQueryKees
PLease share your actual Copy/Pasted M code. The code you shared has syntax errors and non-existing colum names.... So not possible to debug.
- adudani
Memorable Member
hi Anonymous ,
I understand data privacy issues.
If you could provide a sample input and output, masking sensitive information in a usable format ( tables, excel, csv, gdrive/ onedrive etc. link to pbix ) , would be happy to support.
for reference:
Hi Anonymous ,
I tried to reproduce the whole step and it works properly in my case.
Could you check “Query1” again to make sure that the last result it returns does not contain the date 21/10/2024?
Here's my test file.
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
