Forum Discussion
Merge two tables by value range
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!
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. 🙂
The idea is from Excelisfun but i think it is more clear