Add Column (Currency Conversion) based on data of two tables

[adieball]

Hi

I need this in Excel (PowerQuery), but AFAIK it's the same language (M) as in PowerBI, hence asking here.

I have two tables.

BUDGET

CURRENCY_BUDGET

Budget holds

COUNTRY, BUDGET_REVENUE, BUDGET_MARGIN, VENDOR, YEAR, MONTH

Budget_Currency holds:

COUNTRY, LOCAL_CURRENCY, YEAR, CONVERSION_RATE

The "Budget" Table holds the values for Margin and Revenue in Local Currency. The "Budget_Currency" Table holds the conversionrate to EURO as a fixed value per year and local currency. 

As there was no way to link the two tables (was Many:Many), I already added a column called "CountryYear" which 1:Many (Budget_Currency:Budget) and linked the two tables

Somehow I'd need to add a column to "Budget" which holds the EURO Value of the Revenue and Margin based on the information from the "Budget_Currency" Table.

How can I get there?

Or is it better to use a DAX measure instead of adding a column?

[v-chuncz-msft · Accepted solution]

@adieball,

You may use LOOKUPVALUE Function to add a calculated column in DAX.

http://community.powerbi.com/t5/Desktop/Excurr/m-p/361854#M163415