Forum Discussion

Centaur's avatar
Centaur
Helper V
2 years ago
Solved

Using a Foreign Exchange Table

Hello,

 

I am importing FX rates from a website. 

I now want to use those rates to update a [USD] column in my table. 

 

i am not sure how to do this.  

Any assistance is greatly appreciated. 

 

 

 

Need Help
Tips and Tricks

6 Replies

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    2 years ago

    HI Centaur 

     

    Is the USD column the result of calculating a Amount column of a different currency in your dataset with the FX Rate?

     

    As there is litte information, I will expalin what i do.

     

    I make transformations in Power Query. 

     

    If your Transaction Table has a Transaction date, Currency Code and your FX table (I presume is USD conversion table?) This will have all rates for each date for each currency? 

     

    The next part depends if there is a row for each currency rate or a column for each currency.

    If there is a column for each currency, you need to select all columns with a currency and then unpivot these columns. Rename Attribute to CurrencyCode and value to Rate. Make sure the Currency Codes are in the same format in both tables. as in USD, GBP .... and not in one table US, GB

     

    Merge the FX table with the Transaction table on the Date and the Currency Code. Expand the column with just the rate. If you have transactions in your transaction table in USD, then there will be null values in the new rate column. If there is also a currency in the table that is not in your FX table, then there will be null also. If it is only USD, Highlight one of these null values and replace with 1.

     

    Add a cutom column and write this (Replace your actual column names)

     

    if [CurrencyCode] <> "USD" then [Amount]/[Rate] else [Amount]

     

     
    Thanks

    Joe

     

    If this post helps, then please Accept it as the solution

    • Centaur's avatar
      Centaur
      Helper V
      2 years ago

      Hi Joe, thank you for the response. 

      The FX rate is not based on a date.  I simply have a table with FX rates.

      What I have in power query is:

      FXRates (this is the table):

      The table I need to populate with a USD equivalent is

      tblV1:

       

      I am thinking its a lookup of some sort?  

      Not sure how I could use the formula from above

      I would need to reference the second column in that FXRates table though

      I hope I have provided sufficient information. 

      Let me know if you have any other questions. 

      thank you very much.