Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
niekdp
Frequent Visitor

values incorrect while merging columns

 

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

2 REPLIES 2
MarkS
Resolver IV
Resolver IV

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

BalaVenuGopal
Resolver I
Resolver I

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 Price Rate.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.