Please share suggestions for handling many to many table relationships in Power BI. How can they be avoided?
For example: Order table contains orders placed by multiple customers (who are listed in the Customers table), and a customer may place more than one order.
Kind regards,
Julie Bunavicz
Tips for avoiding many to many relationships in Power BI.
In Power BI, a many-to-many relationship is usually 'dangerous' to use.
For you example, you have a customer table(PK (Primary Key) = CustomerID) and a order table with a FK(Foreign Key) = CustomerID. For this example, most of time in real life, we require the relationship to be 1-to-many, i.e. 1 customer can place multiple orders, and 1 order should be attached to 1 customer.
If you connect the PK from customer table to FK in order table, and you get a many-to-many relationship, that means your customer table's customerID (PK) is duplicated. You may remove the duplicate PK rows from customer table if it is allowed.
What I mean allowed here is that, the removed duplicated rows in customer table is a complete duplicate of the whole row. If the duplicated rows of the customer table are not exactly the same, it will be very important to investigate why this happens, and what each of the rows really represent.
Furthermore, if the duplicated PK in customer table is not simply row duplication, but implies other issues of the data, e.g. two different customers sharing identical customer ID (PK). Even if this is not allowed, but could happen in real world. For this situation, you may need to find out the correct Customer ID for each of them, or for some situations, you may need to create a new PK.
In Power BI, if you have to consider many-to-many relationship, you could view this video for 'Bridge table' which is at 3min 30 sec. https://www.youtube.com/watch?v=dEgBOsQGHBA.
Hope this answer is helpful.
Kind regards