Forum Discussion
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 Custom1In 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.
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.