When to use Merging or Table Relationships

[akhaliq7]

I have a issue whereby when I join two tables i get a many to many relationship. To avoid this is it better to merge the tables or the last time I had this issue I created an extra table with unique values. Linking the two tables to the extra table.

[speedramps · Accepted solution]

Hi akhaliq7 

In the relationships screen you can edit the relationship.

But if your 2 tables do indeed containing many-to-many keys then it wont allow a 1 to many.

The usually workarround is to create a bridge tables.

Simply append the key columns from table1 with the key columns from table2 to create table3.
Remove duplicates from table3.

Then create 1:M relationship from table3 to table1   and table3 to table2 with bi-directional filtering.

When you filter table1 it will filter table2 and vice versa.

There are lots of you tube video on how to handle a many to many with a bridge table.

Or you can merge the two tables into one table with Power Query as you described.

Remember we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !

[akhaliq7]

Thanks for taking out the time to write such a detailed response.