Merge two tables by value range

[lpd82]

Howdy!

I have two tables:

Table A

Name	Amount
Bob	1.000
Joe	2.500
Billy	3.000
Frank	3.500
Joy	6.000

Table B

Amount	Rates
0	0
1.125	1
2.000	2
3.000	3
4.000	4
5.000	5

Result:

Name	Amount	Rates
Bob	1.000	0
Joe	2.500	2
Billy	3.000	3
Frank	3.500	3
Joy	6.000	5

How do I merge the two tables and bring the rates over to table A based on the Amount on table A that is less than or equal to the amount of table B?

TIA

[serpiva64 · Accepted solution]

Hi,

to obtain this

you need to add a custom column

(OuterTable)=> List.Last( Table.SelectRows( Rates, (InnerTable)=> InnerTable[Amount]<=OuterTable[Amount])[Rates])

it is better also to sort column Amount in Rates and to buffer the table to optimize the query

= Table.Buffer( #"Sorted Rows")

You can find a fantastic explanation of it (which i have apllied here) in 

Free M Code Class from Basic to Advanced: Power Query Excel & Power BI, Custom Functions 365 MECS 12

https://www.youtube.com/watch?v=3ZkIwKBVkVE

by Excellisfun

It is the last argument of a long video

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[sevenhills]

@serpiva64 , 

Typically, I do this way with "each" ...
Table.AddColumn(Source, "Rates", each List.Last( Table.SelectRows(TableB, (x) => [Amount]>=x[Amount])[Rates]), Int64.Type)

It is interesting that you used (OuterTable) => ..., I learned a new way. 🙂 

[serpiva64]

The idea is from Excelisfun but i think it is more clear

[lpd82]

Thank you very much, I watched your video which helps me understand the logic better.

[serpiva64]

Hi, 

i think there are some typing error in your table

Probably table B first value is 1000

and

Bob result is 1125 not 1000 

or something similar, can you confirm it?

[lpd82]

ah! sorry about that.  I fixed table A.  Thanks