How to execute calculation based on specific row value from different data source

Hi Community,

I am stucking here in this topic and went through quite some web support including youtube video. Must appreciate if someone could help.

Excel file 1. Sales record from (past year, month, week day) including price with currency code.

• most items are recorded in USD, but some items are recorded in alternative currency, the currency code are left empty(Cap1)

Excel file 2. Fx rate from previous month, will be updated monthly by our financial.

Now here comes my task.

I have to create a report showing weekly result, yearly, monthly, weely, as well as month to date. But the mentioned items above somehow need to be mulitplied with the monthly fx rate.

Currently I created two Power query link to the excel files. Now, I need to find a way to let it do the math with corresponding rate and then the output should be part of the report.

The logic behind should be (my current assumption)

If the currency code <> USD, then revenue =  revenue in altnertive currency * fx rate from the correponding month.

Thank you very much.