Forum Discussion

SevanT's avatar
SevanT
Frequent Visitor
1 year ago
Solved

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 ...
Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    1 year ago

    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

SevanT's avatar
SevanT
Frequent Visitor
1 year ago

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's avatar
Omid_Motamedise
Super User
1 year ago

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