Get a value from another table base on date range

[DanielCruzO]

Hi Everyone. 

I am struggeling with the following example.

I have a table with Currency Exchange rates assigned base on dates, the format of my table is the following:

i have a column named "Starting_date" and my dates are the following:

for example, if any transaction is done in 2022-04-01 or before the next date which is 2022-05-01, the rate that we should get is 1.2658.

i need this lookup in another table, where my transactions dates could fall in between any of those dates and retrive the correct currency exch rate.

could anybody help me with this code? i am getting errors in all the ones i have tried.

thank you!  

[edhans]

Try this code @DanielCruzO 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtM31DcyMDJW0lEKDXZRitWJVjLRNzJGFzPVNzRFEYsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Currency = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    AddExchangRate =
        Table.AddColumn(
            #"Changed Type",
            "Exchange Rate",
            each
                let
                    varCurrency = [Currency],
                    varStartDate = Date.StartOfMonth([Date]),
                    varEndDate = Date.EndOfMonth([Date])
                in
                Table.SelectRows(
                    ExchangeRates,
                    each [Currency] = varCurrency
                        and [Date] >= varStartDate
                        and [Date] <= varEndDate
                )
        ),
    #"Expanded Exchange Rate" = Table.ExpandTableColumn(AddExchangRate, "Exchange Rate", {"Rate"}, {"Rate"})
in
    #"Expanded Exchange Rate"

Here is the code for the "ExchangeRate" table referred to.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31DcyMDJW0lEKDXYBkoZKsTrRSmbowkZgYXN0YWOwsIm+EaqwiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Currency = _t, Rate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Rate", Currency.Type}})
in
    #"Changed Type"

Here is how it looks:

Exchange Rate table:

Final Transaction Table:

What it does is stores the Currency type in varCurrency. Then it takes whatever date is i the transaction table and creates a start and end date for the month. so Row 2 has April 23, 2023. This will put April 1, 2023 in varStartDate and April 30, 2023 in varEndDate.

I then added a new column with a Table.SelectRows against the Exchange Rate with the filters to select the proper currency and the date ranges.

That brings in a table:

Just expand the Rate columnn from the Exchange Rate column.

Note: this will not perform well as you get into the tens or hundreds of thousands of rows. Power Query isn't great at table scans. DAX does it faster. Much MUCH faster. It is designed for it.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.