Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear,
I have a table with exchange rates per day. An example:
Exchange rate currency date
1.14 usd 01/01/2018
1.40 cad 01/01/2018
0.90 gbp 01/01/2018
1.15 usd 02/01/2018
1.42 cad 02/01/2018
0.88 gbp 02/01/2018
1.20 usd 03/01/2018
1.44 cad 03/01/2018
0.86 gbp 03/01/2018
So as you can see, the exchange rate to our base currency (EUR) changes per day.
We also have another table with transactions per day.
So you can have multiple transactions in different currencies per day.
To merge this colums, I made a key.
For the exchange rate column it is p. ex: 03/01/2018GBP
In the transaction colum, I also made this key 03/01/2018GBP.
What I want to do is to have the exchange rate from the correct date and currency next to the amount the was paid in the transaction column. But if I merge these columns, Power Bi only shows me the average of GBP.
So I see every time :
01/01/2018GBP 0.88
02/01/2018GBP 0.88
03/01/2018GBP 0.88
Which isn't correct. Is there somebody who could find the solution for my problem?
(So to see the correct exchange rate per currency per day next to the amount in the transaction column)
Kind regards
Niek
HI @niekdp,
It looks like you are wanting to merge Queries (not Columns).
In the Query Editor choose the Merge Queries button then select your transaction table in the top and the exchange rate table in the bottom. You can merge on more than one key so you do not have to make your own key in each of the other tables, just select the date field in both tables and then hold the control key and select the Country Code (you will see little numbers in the headers and just make sure they match).
Then just expand the Table column that is added and select only the Rate column
Hi @niekdp ,
Could you please explain me clearly why you need this value at query ediotor,
If you need this column in transaction table use realted function to get this column into transaction table that you can do once data is loaded from the table add new column
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |