Remove duplicates based on multiple criteria (2)

[Neseren]

Hi 

I have some data that consists of duplicates. 

These duplicates needs to be removed on multiple criteria

I have already used the "grouped by" function to specify the multiple criterias.

But when i do this i get an error message on a small amount of cells saying this: 

Expression.Error: The number is out of range of a 32 bit integer value.
Details:
ex: 43,2

The whole dataset consists of decimal numbers approx within the same range, which is why i do not understand the issue on only a handfull of cells.

As far as i could google it may be the formula in the criteria setup which do not support decimal numbers, but i am not able to solve the puzzle myself, which is why i reach out to you guys.

The dataset looks like this:

The criteria formula looks like this:

= Table.Group(#"Changed Type", {"Batchnummer", "Nedre grænse", "Øvre grænse", "Target Value"}, {{"Resultat",
(x)=> [
t0 = List.Max(List.Transform(x[Nedre grænse],Number.From)),
t1 = List.Min(List.Transform(x[Resultat], Number.From)),
t2 = List.Max(List.Transform(x[Resultat],Number.From)),
t3 = List.Max(List.Transform(x[Øvre grænse],Number.From)),
Resultat = if t2 > t0 and t1 > t0 then t1 else if t2 < t0 and t1 < t0 then t2 else if t0 = t1 then t1 else if (List.Count( {t1..t0}) + List.Count( {t1..t0})) < (List.Count({t3..t2}) + List.Count({t3..t2})) then t1 else t2
][Resultat]}})

Hope you guys could help me 😄

[Neseren]

Can anyone solve this?

Still havn't got a sollution.

Thanks 🙂

[Anonymous]

Hi @Neseren ,

you can use Number.Round to ensure that the values are within a manageable range before performing operations on them

= Table.Group(#"Changed Type", {"Batchnummer", "Nedre grænse", "Øvre grænse", "Target Value"}, {{"Resultat",
(x)=> [
t0 = List.Max(List.Transform(x[Nedre grænse], each Number.Round(Number.From(_), 2))),
t1 = List.Min(List.Transform(x[Resultat], each Number.Round(Number.From(_), 2))),
t2 = List.Max(List.Transform(x[Resultat], each Number.Round(Number.From(_), 2))),
t3 = List.Max(List.Transform(x[Øvre grænse], each Number.Round(Number.From(_), 2))),
Resultat = if t2 > t0 and t1 > t0 then t1 else if t2 < t0 and t1 < t0 then t2 else if t0 = t1 then t1 else if (List.Count({t1..t0}) + List.Count({t1..t0})) < (List.Count({t3..t2}) + List.Count({t3..t2})) then t1 else t2
][Resultat]}})

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Neseren]

Hi

I was wondering if that could be the sollution as well.

Unfortunately i still get the same error message on a handfull of cells:

[Neseren]

Just to make it clear, the duplicates is in the column "Batchnummer".