Forum Discussion
Function currency conversion
Hi Community,
I am new here and have been using PowerQuery more and more.
Currently I am struggling with finding a solution to convert amounts from one currency to another. I have created a web query pulling exchange rates for a set date and set currencies (EUR, etc.) in a table. This table is stored centrally since I am using it for repeatedly for different purposes. Something like this:
| Currency | FX to EUR |
| EUR | 1 |
| USD | xxxx |
| GBP | xxxx |
In a second table I have transactional data, i.e. an account balance with the additional currency detail.
| Supplier ID | Supplier Name | Currency | Amount |
| 1 | A | EUR | 100 |
| 2 | B | USD | 100 |
| 3 | C | GBP | 100 |
So far I always just merge the tables to add FX to EUR column, convert the Amount to EUR in a new Column and delete the FX to EUR column. I would like to create a function that I can re-purpose repeatedly for other files as I am doing these kind of conversions regularly.
How would I go about this?
Thank you for any knind
You can use Record.Field as below
(sourceCurrency as text, Amount_Currency as number, column as text )=>
let
Source = tbFXRates,
FXRate = Record.Field(Table.SelectRows(Source, each ([Currency] = sourceCurrency )){0},Column),
Conversion = Value.Divide(Amount_Currency,FXRate)
in
Conversion
4 Replies
Use the following code.
Just replace the Source table with your currency rate table
(Currency )=> let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg0NUtJRMlSK1YlWCg12AbIrgADMdXcKgHNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Currency = _t, #"FX to EUR" = _t]), #"Filtered Rows" = Table.SelectRows(Source, each ([Currency] = Currency ))[FX to EUR]{0} in #"Filtered Rows"Omid_Motamedise Thank you for quick reply.
This is your code adjusted a little to fit my needs:
(sourceCurrency as text, Amount_Currency as number )=> let Source = tbFXRates, FXRate = Table.SelectRows(Source, each ([Currency] = sourceCurrency ))[FXRate EUR] {0}, Conversion = Value.Divide(Amount_Currency,FXRate) in ConversionThere is one addition I would like to add to the code, which I cant figure out. As of now FXRate EUR is hard coded to the function. I would like to be add the column as a parameter so I can adjust the source and target currency as needed. How would I add [FXRate EUR] as a parameter in the function?
You can use Record.Field as below
(sourceCurrency as text, Amount_Currency as number, column as text )=>
let
Source = tbFXRates,
FXRate = Record.Field(Table.SelectRows(Source, each ([Currency] = sourceCurrency )){0},Column),
Conversion = Value.Divide(Amount_Currency,FXRate)
in
Conversion
If your exchange rate table is accessed in real time through the network, then due to some complex mechanism problems of power query, you may get different results each time you query the exchange rate.
If your exchange rate table is stored on the local computer, then there is no such problem.
Assuming that the exchange rate table does not need to consider the problem of real-time update and the currency type is not repeated, you can use the following syntax instead of the Table.SelectRows function:
//Table.SelectRows(Source, each ([Currency] = sourceCurrency ))[FXRate EUR] {0} Source{[Currency = sourceCurrency ]}[FXRate EUR] // if not existed sourceCurrency, eg: RMB, should add try...otherwise try Source{[Currency = sourceCurrency ]}[FXRate EUR] otherwise error "Not Exist"
