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
Kesh
New Member

Matching a couple of culumn between Tables

Hi,

I don't know how to match 2 couple of data:

for example

I have 2 data sources:

- the first is a sql table with a trip info (city start, city end, miles between the two of them, and other culumns)

- the second is a excel document with simply the price ofthe travel beteween 2 cities the price of the trip( city start, city end, price)

 

I would like to have a table in power bi with:

city sart, city end, price, miles, and many other culumn from teh sql db

 

I tried to do this with relationships with tables but only one is allowed between 2 tables.

How could I do that ?

 

kind regards

1 ACCEPTED SOLUTION
tex628
Community Champion
Community Champion

You should be able to solve this with either a merge between the tables or a relationship. 
Either way you need to connect the two tables with a combination of start/end locations. 

Combine your two columns, for example:

ChinaUK 240$
BrazilSweden1100$

to ->

ChinaUK ChinaUK240$
BrazilSwedenBrazilSweden1100$


Do the same in your other table and then either merge the two tables or create a relationship between the two new columns.


Connect on LinkedIn

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

Hi @Kesh ,

 

Concanate both the columns in both tables and then create relationship between newly created column.

 

To concate 2 columms, use the below statement

 

New Column = CONCATENATE(Start City, End City)

 

use same formula to create a new column for another table and then create relationship.

 

 

@Anonymous 

@tex628 

Nice trick, it works perfectly, is ther a more elegant way ?

 

thank you both of you

 

tex628
Community Champion
Community Champion

With the information you provided I believe this is the best solution. But considering the nature of the data i would assume that there is a unique ID for each trip which you could use in the relationship instead of the combined column. That would in my opinion be the best way to do it.


Connect on LinkedIn

No I can't because the first table is a list of operations made by a driver. So the same travel can be done 10, 100 or more times.

That lead to my following question, I would like to have the sum of my new culumn. each time I ask for sum in tne report I have the sum of all the lines in my excel worksheet. I want the sum of all the prices in the culumn and be allowed to filter by, city start / city end

image.png

 

 

tex628
Community Champion
Community Champion

It looks like your prices are in text format, which would be the reason as to why they are not aggregating.


Connect on LinkedIn

yes it can aggregate.

For the moment I only have 2 prices for 2 travels:

lavera aubagne 500 and lavera chanbery 350.

If I ask for aggregate sum, it pushes 850 (500+350) to all rows.

my goal is to have (in the previous image) 500 + 500 +500 +350 +350+...

 

and the sum would be updated depending the city start/end selected

 

I don't understand why I have 850 each time and not a global sum...

Could you helpme please ?

tex628
Community Champion
Community Champion

Take the combined column that you made in the previous step, add it as the rows dimension to a matrix. 
Then take the price column and add it as the value, make sure that you have it summed. 

Take a picture of the matrix and post it here if it doesnt work!


Connect on LinkedIn

even if it is at th end of the culumn or in another visualisation.

Anonymous
Not applicable

Then, as per me, the concatenation of "start city" and "end city" will be the good approach.

 

Thanks,

Sunil

tex628
Community Champion
Community Champion

You should be able to solve this with either a merge between the tables or a relationship. 
Either way you need to connect the two tables with a combination of start/end locations. 

Combine your two columns, for example:

ChinaUK 240$
BrazilSweden1100$

to ->

ChinaUK ChinaUK240$
BrazilSwedenBrazilSweden1100$


Do the same in your other table and then either merge the two tables or create a relationship between the two new columns.


Connect on LinkedIn

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.