Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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:
China | UK | 240$ |
Brazil | Sweden | 1100$ |
to ->
China | UK | ChinaUK | 240$ |
Brazil | Sweden | BrazilSweden | 1100$ |
Do the same in your other table and then either merge the two tables or create a relationship between the two new columns.
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
Nice trick, it works perfectly, is ther a more elegant way ?
thank you both of you
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.
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
It looks like your prices are in text format, which would be the reason as to why they are not aggregating.
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 ?
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!
even if it is at th end of the culumn or in another visualisation.
Then, as per me, the concatenation of "start city" and "end city" will be the good approach.
Thanks,
Sunil
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:
China | UK | 240$ |
Brazil | Sweden | 1100$ |
to ->
China | UK | ChinaUK | 240$ |
Brazil | Sweden | BrazilSweden | 1100$ |
Do the same in your other table and then either merge the two tables or create a relationship between the two new columns.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |