Function currency conversion

[SevanT]

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

[ZhangKun]

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"

[Omid_Motamedise]

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"

[SevanT]

@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
    Conversion

There 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?

[Omid_Motamedise · Accepted solution]

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