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!

[ichavarria]

I would suggest you use the 'List.Transform' to return a list of values instead of records. Here is a revised version of the function:

RoundToNearest = (input as number) =>
let
numberList = {1, 3, 4, 10},
closest = List.Min(List.Transform(numberList, each Number.Abs(_ - input)) + {Number.Abs(numberList{0} - input)}) + input
in
closest

Here, we're taking the absolute value of the difference between the input and each number in the list, and adding the input to the closest value after getting the minimum value from the transformed list. We're also adding the absolute value of the difference between the first number in the list and the input to the transformed list to ensure that the input is rounded to one of the values in the list.

Hope this helps!

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thanks for your assistance! 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

Thanks for your help!

[ichavarria]

What is the error you are getting? Could you share the text of it?

[Anonymous]

[ichavarria]

Hi @Anonymous,

It seems like the error is because you are using the '_' placeholder within the List.Transform function, but you haven't defined it as a record with a 'Value' field.

Here's a revised version of your code that should work:

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

I added the 'Value =' and 'Distance =' to the record that is being created within the List.Transform function. This way, the '_' placeholder will be interpreted as a record with these two fields.

With this modification, your function should work as expected.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

[Anonymous]

Hey Isaac, thanks for your help.

I believe I'd already included the 'Value =' and 'Distance =' on my original function. Actually, I think both versions are identical unless I'm missing something. Do you have any other ideas?

Regards