Using a Foreign Exchange Table

[Centaur]

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. 

[JoeBarry · Accepted solution]

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/3403...

Thanks

Joe

[JoeBarry]

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]

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 · Accepted solution]

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/3403...

Thanks

Joe

[Centaur · Accepted solution]

Hi Joe,

I went ahead and used a sort of lookup.  Took awhile but I got it done. I am sure it coudl be done your way too but I wasnt sure how to avoid the duplicates. 

VLOOKUP in Power Query Using List Functions • My Online Training Hub

[Centaur]

silly me.  Its showing a "1" because they were all USD.  I changed one of them to EUR and the rate changed:  

I added the Custom Column with the formula from above and after I did, the # of records are 4x now it seems.  

 I clicked on Custom.Column thinking there is some setting there and I saw a bunch of fields and I delected them all but the Custom column I created (far right in pic below)

Do you have an idea why the # records is no longer 4 records as it was prior to adding the FX table? 

[Centaur]

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!