How to define if date is in a range

[APEREZ2]

Hi,

I have the following problem. I need to relate the table of prices:

StartingPrice	EndPrice	Product	Unit Price
2021-03-01	2021-05-22	Apple Tree	1.24
2021-05-23	2021-07-31	Apple Tree	1.54
2021-08-01	2022-04-01	Apple Tree	1.80
2021-03-01	2021-05-22	Bonsai	2
2021-05-23	2021-07-31	Bonsai	1.86
2021-08-01	2022-04-01	Bonsai	1.79

With the sales tables, that contains an structure like this:

Product	Quantity	Sales Date
Apple Tree	123	2021-03-22
Bonsai	234	2021-05-09
Apple Tree	100	2022-01-22

How can I relate the two tables to obtain the Unit Price of each order. Because on Excel for me it's clear that with EndPrice column and XLOOKUP could be work, but on Power Query no.

[Vijay_A_Verma · Accepted solution]

Use below formula in a custom column in second table. First table is PriceTbl.

Table.SelectRows(PriceTbl, (x)=> x[StartingPrice]<=[Sales Date] and x[EndPrice]>=[Sales Date] and x[Product]=[Product]){0}[Unit Price]