REMOVING DUPLICATES AND MERGING QUERIES

[olumidef]

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

[v-cgao-msft]

Hi @olumidef ,

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 Team

If 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

[Omid_Motamedise · Accepted solution]

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

[olumidef]

Thank you @Omid_Motamedise , the buffer function perfectly solves the problem 

[Omid_Motamedise]

You are welcome

[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 as 

S0=Source,

Srev=Table.Distinct(Table.Sort(S0,....),....) 

[PwerQueryKees]

Ok. But wouldn't that be a bug in powerquery? Your 2 examples should be equivalent. In both cases S0 should be evaluated first because S1 depends on it...

[Omid_Motamedise]

It might be but not in all the case, consider this example
S0=Source,
S1= Table.Addcolumn(S0,"x",....),
S2=Table.Group(S1,"A", {"m",Table.RowCount})


it is true that s2 is calculated based on S1, but its result does not dependent to the result of S1, if you use S0 also in this function, the result would be the same (adding a new column, does not changed the result of counting the rows)

[PwerQueryKees]

Sorry, can't help you then...

[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. 

[olumidef]

The process is an applied steps on power query and those are the code relating to the steps in questions 

Due to data privacy issue , unable to share the data/references 

Not certain what the syntax error might be also

[adudani]

hi @olumidef ,

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:

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Qu... 

[PwerQueryKees]

Please share your M code and examples of your data.

[olumidef]

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)