Forum Discussion
Remove duplicates based on multiple criteria
Hi
I have a pretty complicated task that i'm not able to crack myself. So i hope you guys can help me out?
I have a dataset consisting of batch, results, lower tolerance and upper tolerance.
The batch can sometimes be duplicated. I need basically to remove the duplicates on the batches. The issue is to set up the rules to remove the right duplicate(s).
The rules are as follows:
- Only one unique batch
- If a unique batch is below or above the tolerance, the value must be kept
- If a duplicate consists of one value within the tolerances and one or more values outside the tolerances. Then the value inside the tolerances shall be kept.
- If a duplicate consists of values only within the tolerances. Then the lowest value within the tolerances shall be kept.
- If a duplicate consists of values only outside the tolerances. Then the value closest to the tolerances must be kept.
Hope you guys can help me.
Thanks. 🙂
and try this
Thanks a lot!
You just solved my problem. Appreciate it 🙂
16 Replies
to know how to do this watch my video
Thanks for this!
However there is still a problem. This was also one of my original sollutions.
In the batch ending with 7021 i have the values 8 and 3.
With your example the value to be kept is 3, but this is outside the tolerances. I need 8 to be the value to be kept in this case.
I added another two lines to specify what i need:
In this case, i want the row with the value 4 because it is closest to the tolerances. With your example it will give me 2.
Does it make sense?ok, pls try again
(x)=> if List.Max(x[Result]) > List.Max(x[Lower tolerance]) then List.Min(x[Result]) else List.Max(x[Result])