Rounding Closest Value in a Column Based on Values in a Column from another table.

[roncruiser]

Hello,

I've been bouncing around this issue for a couple days now with no luck using Power Query.

I'd like to return rounded to nearest values based on values from another column.
My first idea was using the merge function, but that will only work if the values match.
Is there a way to integrate the Number.Round function into the merge function, or is that the wrong approach?

Table 1 (The 3rd column is what I would like returned)

Location	Value	Rounded to Nearest (Return this Column)
A	2	0
B	3	5
C	29	30
D	15	20
E	20	20
F	27	25
G	11	10
H	7	5
I	9	10

This is the reference table used to round to the nearest value.
Table 2 (Values to round to)

0

5

10

20

25

30

Thank You.

[jaipal]

@roncruiser   merge the tables and try this

Number.Round( _ , [Reference Column]) 

Makesure the value columns are Integer type

[roncruiser]

@jaipal 

I'm unsure where to place the Number.Round(_,[Reference Column] when doing a NextJoin with JoinKind.FullOuter.

It works if the numbers match but won't round to the nearest number if the number falls between numbers in the reference table.
Please clarify.

Thank You.