Forum Discussion
Add an exhange rate column from one query to another
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?
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?