Adding/merging a Custom Column based upon a Date Condition

Hi CincyKJ, another solution:

Output

let
    TableTransactions = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcnBCcBACETRXjwvOGrGmFrE/tvIEggs/H963ZJMPnXJEvuHIhQps1o8gjf9YFB3Zh8bmFl1cunOITMv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaction #" = _t, Location = _t, #"Rate Class" = _t, #"Transaction Date" = _t]),
    TableRates = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5BCsAgDETRu7gWJqOJtmcR73+NRkuhBBdC4PNMxkhMeT+pEELUxyqSZv6ljlWbjxoSZat7JQtKUS5QDsp/KwrypHxXeZNF5cTAtcuian4eipxUB/0Sfmo+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Rate Class" = _t, #"Rate Effective Date" = _t, #"Rate Amount" = _t]),
    ChangedTypeTransactions = Table.TransformColumnTypes(TableTransactions,{{"Transaction Date", type date}, {"Transaction #", Int64.Type}, {"Location", Int64.Type}, {"Rate Class", Int64.Type}}, "en-US"),
    ChangedTypeRates = Table.TransformColumnTypes(TableRates,{{"Location", Int64.Type}, {"Rate Class", Int64.Type}, {"Rate Effective Date", type date}, {"Rate Amount", type number}}, "en-US"),
    StepBack = ChangedTypeTransactions,
    MergedQueries = Table.NestedJoin(StepBack, {"Location", "Rate Class"}, ChangedTypeRates, {"Location", "Rate Class"}, "Rates", JoinKind.LeftOuter),
    Ad_RateAmount = Table.AddColumn(MergedQueries, "Rate Amount", each Table.Max(Table.SelectRows([Rates], (x)=> x[Rate Effective Date] <= [Transaction Date]), {"Rate Effective Date"})[Rate Amount], type number),
    RemovedColumns = Table.RemoveColumns(Ad_RateAmount,{"Rates"})
in
    RemovedColumns

I'm new(ish) to this. I did this in PQ in Excel, so set up the two tables Transactions and Rates (blue tables below) and put them in Power Query.

In the Transactions query, I merged the Rates table according to Location and Rate Class and ended up with a column with rates tables with just the relevant Location and Rate Class rows from Rates table.

Then I put together a function which for each row which for each row in the Transaction table:

takes the table in the newly added column,

keeps all the rows before or equal to the Transaction date,

then keeps only therows with the latest (max) of the remaining dates and

returns only the Rate Amount column.

At the moment this function returns a list (of one in this case) which needs to be expanded. I should really have converted this list within the function to plain value by adding {0} to the line:

FilteredRows1 = Table.SelectRows(FilteredRows, let latest = List.Max(FilteredRows[Rate Effective Date]) in each [Rate Effective Date] = latest)[Rate Amount]

making it:

FilteredRows1 = Table.SelectRows(FilteredRows, let latest = List.Max(FilteredRows[Rate Effective Date]) in each [Rate Effective Date] = latest)[Rate Amount]{0}

which would mean you don't need the ExpandedRateAtTheTime step.

As I left it, if there are multiple rates for the same dates they're all returned and you'd get as many rows for that transaction as there are entries in the Rates table for that date/location/rate class; this would alert you to the existence of such errant rows in the Rates table!

The M-code I ended up with:

let
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    fnRateAtTheTime = (tbl,dte)=>
    let
        FilteredRows = Table.SelectRows(tbl, each [Rate Effective Date] <= dte),
        FilteredRows1 = Table.SelectRows(FilteredRows, let latest = List.Max(FilteredRows[Rate Effective Date]) in each [Rate Effective Date] = latest)[Rate Amount]
    in
        FilteredRows1,
    ChangedType = Table.TransformColumnTypes(Source,{{"Transaction Date", type date}}),
    MergedQueries = Table.NestedJoin(ChangedType, {"Location", "Rate Class"}, Rates, {"Location", "Rate Class"}, "Rates", JoinKind.LeftOuter),
    InvokedCustomFunction = Table.AddColumn(MergedQueries, "RateAtTheTime", each fnRateAtTheTime([Rates], [Transaction Date])),
    ExpandedRateAtTheTime = Table.ExpandListColumn(InvokedCustomFunction, "RateAtTheTime"),
    RemovedColumns = Table.RemoveColumns(ExpandedRateAtTheTime,{"Rates"})
in
    RemovedColumns

I'm sure there's a slicker way but I hope I answered your question 'where do I begin…'

My results in the green table: