Forum Discussion
Solved
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 ...
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
rohit1991
Super User
Super Userhi 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
Hi Rohit. that worked! thank you very much.