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

[Watson]

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.

[Anonymous]

Hi @Watson ,

You could try to merge tables in Power Query.

Merge queries (Power Query) - Microsoft Support

Merge queries overview - Power Query | Microsoft Learn

Then you could add a conditional column to get the judgment statement you want.

Add a conditional column (Power Query) - Microsoft Support

Add a conditional column - Power Query | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.