Connection between date and date range

Hi Anonymous 

Suppose you have two tables like below and you want to add a price column to the Shipment table according to the prices in Costs table based on a corresponding price period.

You can copy and paste below codes into a blank query's Advanced Editor to create a function. I call this function FindPrice. It will find a price from Costs table according to a provided parameter OrderDate.

(OrderDate)=>
let
    Source = Costs,
    Custom1 = Table.SelectRows(Source,each [Valid From] <= OrderDate and [Valid To] >= OrderDate){0}[Price]
in
    Custom1

In Shipment table, click on Invoke Custom Function to add a column and use the FindPrice function and Ship Date column to invoke this function to get the prices.

Hope this helps.

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.