Problematic Many to Many Relationship - To many Rows

[StuartSmith]

Can some please help.  I have 2 tables and created a relationship between the country coulmns, but as the relationship is many to many, getting to many rows in a table.

The table should look like this...

but the Bi table is looking like this...

I know this is down to the many to many, and have fumbled around with bridge tables, etc. but lack the knowledge\understanding and hoping someone can help.  

I have created a test pbix file, but unsure how to upload to here as cant see a file upload option, sharing via Dropbox link...

https://www.dropbox.com/s/zmicatnu01w7dan/test.pbix?dl=0

hopefully it will work and thanks in advance.

[amitchandak]

@StuartSmith , you should create Country and region or country table and join that with both the tables. (Bridge Table)

Country = Distinct(union(all(Table1[Country]),all(Table2[Country])))

https://www.seerinteractive.com/blog/join-many-many-power-bi/

[StuartSmith]

Thanks, I sort of managed to get it working, although i had to use the "Carrier" column from the "SIMs" table, which didnt feel right, although worked.

That said, as soon as I added another column, such as "Contract End Date" it doubled up the rows again.

 Any ideas as this is so annoying, but I guess something so simply.  I have updated the file in dropbox.

https://www.dropbox.com/s/zmicatnu01w7dan/test.pbix?dl=0

Thanks in advance for your help.

[StuartSmith · Accepted solution]

Might have found a solution to the many to many relationship issue producing multiple rows.  I have created a new uniqiue column in each table using...

Key = 'Carrier Matrix'[Region] & " - " & 'Carrier Matrix'[Country]& " - " & 'Carrier Matrix'[Carrier]

and this now created a 1 to many relationship and seems to work, although need to test before I get to excited.  Unless you can see an issue with this approach?

[Greg_Deckler]

@StuartSmith No, creating a key column is definitely the way to go if you can't merge the tables in Power Query for instance. Definitely try to avoid M2M relationships!! Either form a combo key column like you did or create a "bridge" table that contains unique values (in the even that you can'd do a combo key). I personally try to do a combo key first and then a bridge table second.

[StuartSmith]

Cheers guys, the creation of the "Key" column on both tables seems to have fixed the issue and infact, actaully helped me identify columns on either table that had mis-matching values, such as typo's as they couldnt create a relationship between tables.  Once the typo's had been fixed or row content matched, duplicate columns vanished. Happy days. 

This has been a valuable learning lesson and after trying to fix the issue all week at work, the eurka moment actually came late Friday night, when I shouldnt have been working. 😀