Forum Discussion
Lookup a Currency Rate according to a Date
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.
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
4 Replies
- Akash_Varuna
Super User
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 
rohit1991hi 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 AddUSDColumnHi Rohit. that worked! thank you very much.
