Lets begin with the objective (End Result) in mind
In Excel we can use an Index Match Array Formula to get the correct price.Click here to download the Excel File
{=INDEX(Rates[Price],MATCH(1,([@Customer]=Rates[Customer])*([@Date]>=Rates[From Date])*([@Date]<=Rates[To Date]),0))}
More details on Excel Array formulas can be found in this post
USING QUERY EDITOR / POWER QUERY
Step #1: Go to Transactions Table and Merge it with the Rates Tables using Using Common Customer Field
After merge, We will get the corresponding rows for each customer from the Rates Table inside the Transactions Table.
Once you select any Table, you can see the related rows in the “PREVIEW” section. This is depicted in the picture below
Step #2: Add a custom column to get the required row i.e. the row where DATE in the transactions table falls between the Start and End Dates of the Rates Table:
We can use Table.SelectRows function to get the desired row
Step #3: Final step is to simply expand this Custom Column and select the desired “price” Column
THE FINAL “M” CODE
let
Source = Excel.CurrentWorkbook(){[Name="transactiontbl"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Date", type datetime}, {"Qty Purchased", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Customer"},Rates,{"Customer"},"Rates",JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each let mydate= [Date] in
Table.SelectRows([Rates],each [From Date] <= mydate and [To Date] >= mydate)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Price"}, {"Price"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rates"})
in
#"Removed Columns"
You can also follow the steps from the Query Editor of the Excel File (which you downloaded above)
USING DAX
Using DAX, we can add a calculated column in Transacations Table using the common CALCULATE and FILTER combination as follows
Price =
CALCULATE (
VALUES ( Rates[Price] ),
FILTER (
Rates,
transactiontbl[Customer] = Rates[Customer]
&& [Date] >= Rates[From Date]
&& [Date] <= Rates[To Date]
)
)
