Forum Discussion

Centaur's avatar
Centaur
Icon for Helper V rankHelper V
3 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
JoeBarry's avatar
JoeBarry
Icon for Solution Sage rankSolution Sage
3 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
    Icon for Helper V rankHelper V
    3 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. 

     

    • JoeBarry's avatar
      JoeBarry
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Hi Centaur 

      For this case in Power Query, Merge the FX table to tblV1 just on the Currency column. Since you have USD in your FX table as 1, then you can leave out the step replace null values with 1.

       

      If this report you are creating is not a one off and you need constant rates, please refer to my post https://community.fabric.microsoft.com/t5/Power-Query/web-content-csv-file-access-forbidden/m-p/3403521/highlight/true#M110756

       

      Thanks

      Joe

      • Centaur's avatar
        Centaur
        Icon for Helper V rankHelper V
        3 years ago

        Hi Joe, I am making progress.  I joined the FX table as prescribed and I think I need to expand on the FXRates as below  by clicking on the arrows and select USD Per Unit and after I do this I see a "1" for the rate and ot the actual rate. It is decimal format.  I am not sure if the solution is to "ignore null values" as you mentioned above but I do not see that option.  

         

        What do you suggest as the next step?  thank you!