August 26, 2020
One query contains currency and exchange rate with reference to USD currency.
Another query contains transacted amount in native currency and i need to calculate the amount in USD currency.
Currency Amount USD Amt
SGD 341 ____
October 5, 2010
Always best to provide your workbook and data so we don't have to recreate everything from scratch.
If your conversion rate is the output of another query then you can create a custom column and use the rate by referring to the name of the query. For example;
#"Added Custom" = Table.AddColumn(#"Changed Type", "USD", each [SGD] * USDRate)
takes the values in the column SGD and multiplies them by the output (the exchange rate) of the query USDRate, resulting in a new column called USD.
You can also create a custom function and either hold the exchange rate in that, or read the rate from a cell.
Then by invoking the custom function and multiplying the rate by the SGD, you'll get the same results.
In the attached file I've included examples of both methods.
August 21, 2019
Attached a simple solution
first query, Data>New query>from other sources > From Web, input https://www.xe.com/currencytab.....rom=USD, or if you have the currency table just bring in to Power Query Editor
2nd query is to bring in the input amount
then merge with the currency table to get the daily conversion for SGD to USD
just output the converted USD back to Excel
The macro is good to have (save some steps of right click refresh)
you can try input amount in A2 and click Convert