Table Join problem

[AutismRunner296]

My intelligence is sorely lacking today. I am trying to join two tables from excel on my visualisation, and I can't seem to get them to do what I want.

I have a sports team table showing a list of players with some overall stats, and I'd like to join that to a table that shows the individual scores, where the game was, and the name of the opponent. Each entry in that table has a unique key (A, B, C.....)

The first screenshot shows the scores from Game A in the final column.  Is there a way that I join the tables so that Power BI knows which location and which opponent it is, by selecting the main table and highlighting column A perhaps, or B and so on?

On the flip side, if I chose the location in Table B can I show the games in the main table that only show that location?

If anyone can decipher what I'm getting at that would be great.

[AbhinavJoshi]

Hello @AutismRunner296. You can either merge these tables in Power Query M using the Primary - Foreign key or the key that is in common. The best option is to create a relationship between these two. To do that, navigate to Model View. Once you are there it will show your tables, in the top bar click on Manage Relationships. Once there try clicking on Autodetect and see if Power BI joins them correctly for you. If it didn't work, click on "NEW" and  join them manually again using the Primary - Foreign key or the key that is in common. Once you have selected the tables, ensure to set the cross filter direction to both, this ensures you can filter both the tables in vice versa order. I hope it helps! 

[AutismRunner296]

Looking at it a bit more, it's quite possible I have over complicated the excel tables. The point here is to use Power BI to do the magic, not in Excel and then try and use Power BI to decipher it.