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

ZhangKun's avatar
ZhangKun
Super User
1 year ago

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"