Forum Discussion
Round to nearest value in a list
Good morning!
I'm trying to create a function that takes a number and rounds ir to the closest number on a given list of numbers in power query.
This is what I've got so far:
RoundToNearest = (input as number) =>
let
numberList = {1, 3, 4, 10},
closest = List.Min(List.Transform(numberList, each [Value = _, Distance = Number.Abs(_ - input)]))[Value]
in
closest
However, this is giving me errors.
Any ideas?
Thanks!
That function wasn't giving me quite the right values, but I used it as a base to come up with the following:
RoundToNearest = (input as number) =>
let
numberList = {10, 2, 3, 4, 5},
difference = List.Min(List.Transform(numberList, each Number.Abs(_ - input))),
closest = if List.Contains(numberList, input + difference) then input + difference else input - difference
in
closestEssentially, I first get the difference between the input and the closest match on the list. Similar to before but only the difference (without the record that included value).
Then, because I used the absolute value, I excecute the if-statement to check whether I need to add or subtract the difference from the input, based on which of the two would give me a value on the numberList
2 Replies
You may use this
closest = [a = List.Transform(numberList, each Number.Abs(_ - input)), r = numberList{List.PositionOf(a,List.Min(a))}][r]That function wasn't giving me quite the right values, but I used it as a base to come up with the following:
RoundToNearest = (input as number) =>
let
numberList = {10, 2, 3, 4, 5},
difference = List.Min(List.Transform(numberList, each Number.Abs(_ - input))),
closest = if List.Contains(numberList, input + difference) then input + difference else input - difference
in
closestEssentially, I first get the difference between the input and the closest match on the list. Similar to before but only the difference (without the record that included value).
Then, because I used the absolute value, I excecute the if-statement to check whether I need to add or subtract the difference from the input, based on which of the two would give me a value on the numberList
