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 Useron 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,....),....)
- 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...
Thank you Omid_Motamedise , the buffer function perfectly solves the problem
- Omid_Motamedise
You are welcome
