Round to nearest value in a list

[Anonymous]

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!

[Anonymous · Accepted solution]

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
       closest

Essentially, 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

[Vijay_A_Verma]

You may use this

closest = [a = List.Transform(numberList, each Number.Abs(_ - input)),
r = numberList{List.PositionOf(a,List.Min(a))}][r]