BI Designer many to many relationship

Question

I am new to Microsoft Power BI designer and noticed this documentation that mentions being able to do a many to many join within the tool. When I try to do the many to many join, I receive an error that says "You can't create a relationship between these two columns because one of the columns must have unique values. Am I doing something wrong?

Thanks in advance.

Answer 1

This is an error in the documentation. As you've noted, you can't specify this in the relationship dialog. Many to many relationships require a junction table to work (a table in the middle that says how the two tables should be correlated.) You may be able to get what you want with a 1:1 or M:1 relationship. In time we'll likely address a way to do this automatically.

Answer 2

There are actually two simple ways to do this in Power BI,

1. use the merge query function if you have messy data(heaps, two spreadsheets etc). That allows you to play with joining to sources of data with multiple columns.

In query editor, select the query you want to see the joined data in. Click Merge Query (select the type of join you want(inner, outer, etc)) and then hold down control and select which columns you want to join from source and destination, it will show the count of hits it is making. Click okay and then select which columns you want to see in the original query.

2. You can also create a calculated column built on multiple columns and use that as the single "key" on each query. I find merge an easier solution since I put the result one primary query and its simpler to utilize in a report.

The reason for this "single" key concept is due to tabular mode\power pivot. MS will probably eventually handle this simply by creating the multi field calc column for you behind the scenes.

Answer 3

This is one advantage of Tableau over Power BI. I had the same problem when trying to create a many-to-many relationship in Power BI Desktop 2.38. The CustomerID in the CustomerHistory table cannot be joined with CustomerID in the Lead table making analysis of data in one table based on the values of the other table difficult.

However, I could easily join the two tables in Tableau Public 10.0: