Calculate currency in different column while ignoring USD

[crissins]

Hello,

I am trying to calculate KSH & STG to USD. The rate table looks like this.

and my main Sales data 

I am concatenating the year and week as a string in both tables to match them but. I am getting empty values in the merge columns with the when exchange rates

my formula to calculate the gross price in dollars is the following.

= Table.AddColumn(#"Rounded Off", "Gross_USD", each if [CURRENCY] = "KSH" then [GROSS_PRICE] / [Rates.KSH_PER_USD]
else if [CURRENCY] = "STG" then ([Rates.USD_PER_STG] * [GROSS_PRICE])
else [GROSS_PRICE])

Thank you very much for taking the time in reading this post this is for a sales report and is my first as a freelancer.

Regards, 

[Ashish_Mathur]

Hi,

The Query Editor is case sensitive.  In the cells where you get null, ensure that in the Currency column, the entry is KSH and STG.  If it still does not help, then share the link from where i can download your PBI file.  If the data was imported from an MS Excel file, then share the download link of that Excel file as well.

[crissins]

Here's my relations