Power BI

Creating Bridge Table

creating-bridge-table

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.

Merge in Power BI training in Sydney

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.

Power BI training in Sydney - Bridge values

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.

Power BI training in Melbourne - DISTINCT

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.

bridge table in power bi training in Melbourne

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.

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *