Merging of queries based on date or number intervals – Table.SelectRows function
This tutorial describes how to connect two queries using number ranges.
I mean not assigning by exact or fuzzy value, but by "smaller than" or "greater than"...
For example, here is a table with sales. I want to assign proper values from another table - proper values based on date from, date to and product.
Use a Custom column in a table with product and write this:
- (firsttable) =>
Table.SelectRows(
pricelist,
(pricetable) =>
pricetable[Product] = firsttable[Product]
and firsttable[Date] >= pricetable[Date from]
and firsttable[Date] <= pricetable[Date to]
With comments:
- (firsttable) => // definition of first variable - first table
Table.SelectRows(
pricelist, // the "other" table with prices
(pricetable) => // definition of second variable
pricetable[Product] = firsttable[Product] //conditions
and firsttable[Date] >= pricetable[Date from]
and firsttable[Date] <= pricetable[Date to]
)
Because of writing into a custom column, erase the word "each" in a formula row - this will ensure the result is a table, not a function.
Expand:
And that’s the result: