Add an exhange rate column from one query to another

[hctrgrc]

Hello, I'm still new to Power Query and I wanted to ask what would be the best solution to the following case: 

I have my first main data set [VENTAS] where I have sales ("Ventas" in Spanish) by date. The sales are split into two columns each one for a different currency (USD) and (Mexican Peso). Some sales are exclusively registered as (Mexican Pesos) and do not include the corresponding USD conversion (Some rows do make the conversion. Those that don't have a default exchange rate of [1]). 

("Tipo_de_Cambio" = Exchange Rate in Spanish)

I have a second query where I have dates and all available exchange rates (this time for USD and Euro currency).

What would the best practice/approach be? Maybe merge them? Ultimately I want to have the exchange rate column from my second query in my first main query (or somehow reference it) so that I can manually make a Measure in Power BI and, for all of those cases where no USD sale conversion was made, calculate it with my second query currency exchange rate. 

Since I have an exchange rate column in each data set, could I just merge them?

I hope I made sense and it's clear enough 😅. 

Appreciate it a lot!

[Caelan · Accepted solution]

So where ventas USD is blank, you need to get the exchange rate on the day of the sale from the exchange rate table, and give the USD value of the sale? I assume you're recording the date of the sale in the sale table?

If you want to do this in power query, I would filter euros out of the exchange rate table, then merge the tables based on the date, and create a calculated column for the USD conversion. You can then delete the exchange rate column if you won't be using it again. Is there any reason why the actual values in the sales table should differ from the Mexican Peso * USD exchange rate? If so, you can keep any actual values with a conditional column. If not, might aswell just calculate all of them?

[hctrgrc]

I was thinking, could a simple relationship between datasets work?

In Power BI in the Model View, make a relationship between the "dates" column and then maybe enter a new column in my main data set where I calculate the sales in Mexican Peso divided by the exchange rate from my second data set?

Given the relationship of dates, would it pull the right exchange rate? Also I would need to take into account that some dates only have the exchange rate for Euro so I would need to make some conditional.  Or is there something like a VLOOKUP function?