Lookup a Currency Rate according to a Date

[Centaur1]

Hello Experts

I have a append query with 3 columns. 

the fields in the append query are Date, Rate, Curr (for Currency).  

I have 4 different currencies:  USD, EUR, GBP, DKK

What I need is to add a column called [USD Amount] in my query called Stampli and this column will lookup the rate based on 3 criteria:  Stampli.[WDDate] and the Stampli.[Currency] but if the currency is USD then use the Stampli.[Amount] (since this is already in USD or could simply use 1 in the calculation for what te currency is USD. 

Example:  if Stampli.[WDDate] is 1/26/25 and Stampli.[Currency] = EUR then calcuate the Amount*Rate but noting that if USD then use a 1 as the Rate. 

The rate would be the rate according to that Stampli.[WDDate] and Stampli.[Currency]

Not sure if I am missing something in my explanation.  

I am a novice user of PQ.  

thank you very much.  

here is a clip of what the Append query looks like:

The lookup formula would lookup the rate in this query. 

[rohit1991 · Accepted solution]

hi @Centaur1 ,

Here's the concise Power Query M code to calculate USD Amount:

let
// Load queries
Stampli = YourStampliTable,
CurrencyRates = YourCurrencyRateTable,

// Merge Stampli with CurrencyRates
MergedTable = Table.NestedJoin(Stampli, {"WDDate", "Currency"}, CurrencyRates, {"Date", "Curr"}, "MergedRates", JoinKind.LeftOuter),

// Expand the Rate column

ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedRates", {"Rate"}),

// Add USD Amount column
AddUSDColumn = Table.AddColumn(ExpandedTable, "USD Amount", each if [Currency] = "USD" then [Amount] else [Amount] * [Rate], type number)
in
AddUSDColumn

[Centaur1]

Hi Rohit.  that worked!  thank you very much.  

[dufoq3]

Hi @Centaur1, again:

Provide sample data in usable format (not as a screenshot) - if you don't know how - read note below my post.
Don't forget to provide also expected result based on sample data.

[Akash_Varuna]

Hi , 

Merge the Stampli table with the Rates table using WDDate and Currency to get the corresponding Rate.

Add a conditional column to calculate [USD Amount]: if Currency = USD, use Amount, otherwise multiply Amount by Rate.

Expand the Rate column from the Rates table.
If this helps please do give a kudos and accept this as a solution