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: