Our attendees of Power BI training in Australia often come up with the problem of linking two tables with many-to-many relationship between them. This issue is addressed immediately by our Microsoft certified trainers in our Power BI training onsite and in our online Power BI training by creating a bridge table. To lend a helping hand for Power BI learning enthusiasts we have organized a blog series covering similar day-to-day problems.
Here’s the link to the blog series covering all such problems: https://powerbitraining.com.au/category/data-modeling-issues-and-solutions/
In this blog post, a step-by-step guide on creating a bridge table to link two tables has been presented. Before we dive into details of the bridge table, here is a list of topics covered so far::
1. Importing Data
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/
2. Data Preparation
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/
3. Creating Date Table
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-3-power-bi-training-in-sydney-power-bi-training-in-melbourne/
4. Setting Model Design
For a step by step guide on this issue: https://powerbitraining.com.au/data-model-common-issues-and-solution-4-power-bi-training-in-sydney-power-bi-training-in-melbourne/
5. Creating Surrogate Key
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-5-power-bi-training-in-sydney-power-bi-training-in-melbourne/
6. Merging Queries
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-6-power-bi-training-in-sydney-power-bi-training-in-melbourne/
7. Creating Calculated Columns
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-7-power-bi-training-in-sydney-power-bi-training-in-melbourne/
8. Flattening the Product Table
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-8-power-bi-training-in-sydney-power-bi-training-in-melbourne/
9. Creating Product Hierarchy
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-9-power-bi-training-in-sydney-power-bi-training-in-melbourne/
10. Active vs. Inactive Relationship
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-10-power-bi-training-in-sydney-power-bi-training-in-melbourne/
11. Working with Role Playing Dimensions
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-11-power-bi-training-in-sydney-power-bi-training-in-melbourne/
12. Adding Higher Levels of Details
For a step-by-step guide on this: https://powerbitraining.com.au/working-with-multiple-fact-tables-power-bi-training-in-sydney-power-bi-training-in-melbourne/
13. Cater for Misleading Interpretations
For a step-by-step guide on this: https://powerbitraining.com.au/dealing-with-misleading-interpretations-power-bi-training-in-sydney-power-bi-training-in-melbourne/
14. Creating Bridge Table
Bridge Tables are created to link two-dimension tables that have many-to-many relationship between them. In our data set, each sales Person sells multiple product types and each product type is sold by multiple sales representatives. Therefore, a many-to-many relationship exists between the Product Table and the Sales Person.
To link these two dimensions, a bridge table is created between these two dimensions. A bridge table has one-to-many relationship with each of the dimension table. A bridge table itself is not a dimension table, it is in fact a table that shows relationship between two dimensions.
To create a Bridge Table:
1. Click on Home > Edit Queries.
2. Right click on the Sales Person Per Product Type Query. Click on Reference.
3. Rename the new query as Sales Person To Product Type.
This table has the missing Person ID column.
4. Click on Merge in the Combine Section.
Merge box opens.
5. From the second drop-down menu, click on Sales Person.
6. Click on Email in the Sales Person To Product Type table.
7. Click on Email in the Sales Person Table. Click OK.
8. Click on the double headed arrow and load only the Person ID
9. Remove the Email, Sales Person First Name and Sales Person Last Name from the table.
10. Click on Close & Apply in the Query Editor.
A many-to-many relationship still exists between the Product Table and the Sales Person To Product Type Table.
11. Click on Modeling Tab in the Ribbon Menu.
12. Click on New Table. Type the following DAX expression in the Formula Bar.
A new table containing a single column containing distinct product types is created.
13. Click on Model in the Left Ribbon.
14. Click on Person ID in the Sales Person To Product Type Table and drag to Sales Person.
A one-to-many relationship is created.
15. Click on Product Type in the Sales Person To Product Type table and drag to Product Type.
A one-to-many relationship is created.
A bridge table is created which solves the many-to-many problem by creating one-to-many relationship with each dimension.
Conclusion:
Creating bridge table is the easiest way to link two tables which have a many-to-many relationship between them. The bridge table itself has a one-to-many relationship with each of the two tables. The bridge table is not considered a dimension table in the data model. In this blog post, a step by step guide on how to link two table via bridge table has been presented.
More Checkouts for Power BI Training in Australia.
Hi. a helpful post. I am curious as to why the distinct table operation was performed in DAX and not Power Query.