Forum Discussion
Remove duplicates based on multiple criteria (2)
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 😄
4 Replies
Just to make it clear, the duplicates is in the column "Batchnummer".
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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:
Can anyone solve this?
Still havn't got a sollution.
Thanks 🙂
