Remove duplicates based on multiple criteria

[Neseren]

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

[Ahmedx · Accepted solution]

and try this

[Ahmedx]

to know how to do this watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RjAp9lOi4-mJWZfuH?e=szbLe1

[Neseren]

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?

[Ahmedx]

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])

 https://1drv.ms/v/s!AiUZ0Ws7G26RjAt0CFj38gO46CJW?e=QvIaTP

[Neseren]

Thanks once again!

You are so close to make it right.

Unfortunately the batches ending on 7021 and 1021 gives me the number 3 in both cases.

I need in only these two cases to get the number 8 and 6.

The reason is, that the result min is outside the tolerance and i therefore need the highest numbers, because it is within the tolerances.

Additional info:

Also if, lets say i have a result above the tolerance and another beyond the tolerance, i need the result closest to the tolerance gap to be kept.

For example:

Here i need 4 and 11 to be kept.

Hope you can make it? 🙂

[Ahmedx]

pls try

[Neseren]

7011 and 3011 is now wrong.

I want the numbers 6 i both cases, because it is the value within the tolerance.

[Ahmedx]

try again

[Neseren]

You're getting really close now.

Only error is 3331. It needs to be result: 4 instead of 13.

Reason is that 4 is closer to the tolerance than 13 is.

[Ahmedx]

try

[Ahmedx]

and is 1 closer to 5 than 11?

[Neseren]

In this case i would like to keep the result: 11.

The tolerance is 5-10.

The distance from 1 to 5 is 4.

The distance from 11 to 10 is 1.

Therefore i would like 11 to be the kept result.

[Ahmedx · Accepted solution]

and try this

[Neseren · Accepted solution]

Thanks a lot!

You just solved my problem. Appreciate it 🙂

[Neseren]

At first sight it seems like 99 and 100 both works.

Can you explain the difference?
I will validate it later today to be sure 🙂

[Ahmedx]

in this case what should happen and why

[Neseren]

In this case i would like 8 to be the kept result.

Reason is that 8 is between the tolerance 5-10 and 3 is outside.