Adding/merging a Custom Column based upon a Date Condition

[CincyKJ]

Hello Power Query Community!

I need help adding and/or merging a Custom Column to a table based upon a Date Condition.

I have a “Transactions” table that contains the following pertinent columns:

Secondly, I have a “Rates” table that contains respective Rate Amounts for all transactions based upon the Location, Rate Class, and Rate Effective Date:

I need to add/merge a Rate Amount column to my Transactions table that references the following criteria:

1. The Location of the Transaction
2. The Rate Class of the Transaction
3. The respective Rate Amount at the time of the transaction based upon the Rate Effective Date column

Example: For Transaction # 6565984 which occurred on 10/03/06, the rate for that period went into effect on 07/03/06 and should be $400.

The added/merged Rate Amount column in the Transactions table should return the following:

Where do I begin in addressing my above task?

Thank you so much in advance for any guidance you could provide!

[dufoq3]

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

[CincyKJ · Accepted solution]

Thank you so much dufoq3.  You're solution worked perfectly for me!  🙂

Respectfully, CincyKJ

[dufoq3]

You're welcome. Regarding Formula.Firewall error - check this .

[p45cal]

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:

[CincyKJ]

Thanks p45cal for your response!  🙂

When I tried to implement your M-Code verbatim in Excel, however, I got the following Warning message:  

Unfortunately, I don't have the required advanced skills to "troubleshoot and/or edit" your solution relative to the warning message.

Respectfully, CincyKJ

[p45cal]

Odd. A pain.

See if anything at https://community.fabric.microsoft.com/t5/Power-Query/Query-references-other-queries-or-steps-so-it-...

helps.