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:

Leave a Reply

Your email address will not be published.

*

clear formPost comment