Forum Discussion
Joining multiple fields between two tables
Hi there,
I have been attempting to solve this issue for awhile but have hit a snag. I'd like some assistance to create a report to populate the points for fields Drive 1, Driver 2, Driver 3, RGMP, Captain and Turbo in Table 1 for the corresponding Grand Prix in Table 2.
For example, the report would have a chart showing:
- Australia
- Phil
- Driver 1: Daniil Kvyat 48 points (points from Table 2)
- Driver 2: Kimi Raikkonen 42 points
- Driver 3: Romain Grosjean -6 points
- Captain: Daniil Kvyat 48 points
Table 1
Table 2
I understand in Power BI only one field can be joined between two tables. In this case, how to join multiple fields between two tables?
Thanks,
Philip
3 Replies
Hello Anonymous
In PowerQuery, you could unpivot these columns before loading it into table 1
Then in both Table 1 and Table 2 create a column that combintes [Grand Prix] and [Driver] then join the two tables on that field
I'd like to create a relationship between the two tables so that points in Table 2 would populate for the corresponding driver in Table 1 for the specific Grand Prix.
Also, I would like to create a hierarchy in Table 1 in the order of Player>Drivers 1, 2, 3 for points scored. Creating a relationship between the two tables is best to achieve this outcome?
Yes, creating the relationship is the way to go. Do you have an .pbix file you with the data that you can share?
