Within data modeling, the cardinality of a join between two tables describes the relationship between the rows of one table and rows in the other. Common cardinalities include one-to-one, one-to-many, and many-to-many.
In a one-to-one relationship, one record in a table is associated with one record in another table. In a one-to-many/many-to-one relationship, one record in a table is associated with two or more records in another table. In a many-to-many relationship, multiple records in one table are related to multiple records in another table, causing ambiguity.
Power BI makes it easy to manage relationships for one-to-one and one-to-many relationships using a primary key, but what happens when you have a many-to-many relationship? While Power BI does allow many-to-many relationships, these types of relationships can yield incorrect calculations and nonsensical results.
One solution to creating a many-to-many relationship is building a bridge table. Bridge tables can help resolve the many-to-many relationship into multiple one-to-many relationships.
This article will walk you through the steps to building a bridge table and features lessons from Data Coach’s Power BI Desktop Creator course.
What is a Bridge Table?
A bridge table is a table that contains one record per unique value in the key field between two tables. That is, if Table 1’s key field has values of {A, C, D} and Table 2 has {A, B, C, E}, the bridge table would contain the values {A, B, C, D, E}. We can then relate Table 1 to the bridge and the bridge to Table 2 — it bridges the gap between many-to-many tables.
Read on to learn how to build one from your already existing data tables.
Bridge Table Use Cases
Many-to-many relationships are not ideal due to their nature of duplicating values, leading to inefficiencies and misrepresentation of data. A bridge table can bridge the gap between a fact table and a dimension at a lower grain or even in multiple dimension tables.
Also referred to as a “factless fact table” in the latter case, they can establish relationships between elements of different dimensions. In both cases, it eliminates duplicates and leads to a more accurate representation of the data.
How to Create A Bridge Table
Step #1: Create References
In Power BI, a query reference looks at the results of a query (i.e., the actions we perform on a data table). By creating a reference, it will update if new values are added at a later date. If we duplicated the tables, we would lose that dynamic updating ability.
Navigate to the power query editor by clicking on transform data. To create a query reference, right-click the table name and select Reference in the menu that appears. This will create a new query named “Table 1 (2).” Repeat this process for Table 2.
Step #2: Remove Other Columns
We are only interested in the fields that relate to our two tables so that we can delete all other columns.
Go to your reference tables and delete all columns that are not your column of interest, which you will use to relate your two tables. This column should appear in both Table 1 and Table 2. You should only be left with one column in each of your reference tables that have the same name.
Verify that the column headers in both reference queries are identical — including capitalization, before proceeding to the next step. Let’s name our column header “Column A.”
Step #3: Append the Reference Queries
This will ensure that all values from both reference tables will be combined — thus, no missing values.
Select one of the references in the Query pane to the left (it does not matter which is selected). Under the Home ribbon in the Power Query Editor, select “Combine” in the upper-right corner, and drop down to the “Append Queries” button. In the Append window, make sure you select the other reference.
For example, if we selected the Table 1 (2) reference initially, we would select the Table 2 (2) reference in the drop-down. When you press OK, Table 1 (2) values will be added to the Table 2 (2) reference. At this point, we can go ahead and rename Table 1 (2) to “Bridge,” or vice versa.
Step #4: Delete Duplicate Values in your Bridge Table
This will ensure that there is one record per unique value to relate your two tables.
In your Bridge table, select the “State” column. Under the Home ribbon in the Power Query Editor, select “Reduce Rows,” > “Remove Rows,”> “Remove Duplicates.” At this point, our bridge table is complete.
We have combined all values from the two original tables and removed duplicates, creating a bridge table with one record per unique value from the two originals. We no longer need the Table 2 (2) reference; we can deselect the Enable Load option.
Right-click Table 2 (2) query and click “Enable Load” to deselect it. This will ensure the Table 2 (2) reference will not appear in the data model in subsequent steps. You can now Close & Apply to view your Bridge in the Data Model view.
Step #5: Manage Relationships
For our final steps, we must enter the Manage Relationships menu. Power BI may or may not automatically detect relationships between Bridge, Table 1, and Table 2. In any case, open the Manage Relationships window.
We can add or edit relationships so that:
Bridge and Table 1 are connected on [Column A] with “Both” for the cross-direction filter.
Bridge and Table 2 are connected on [Column A] with “Both” for the cross-direction filter.
The Bridge is Complete!
You can now use the bridge table to create visualizations, bringing together the values from both Table 1 and Table 2. These bridged values enable you to bring in values from either side of the bridge and aggregate them appropriately to fit your visualization needs.
Closing
This tutorial serves as a simple guide to help familiarize you with bridge tables. Keep in mind there are different approaches to creating a bridge table, depending on what your needs are, that may require advanced calculations and the use of additional columns.
Bridge tables can look different from dimension tables versus fact tables, so be sure to examine your dataset and modify this technique to ensure that your data isn’t misrepresented. For example, dimension-type tables have an ID column, so it is best practice to always use the ID column as the “one” side of a relationship. Alternatively, a Star-Schema is what is recommended for relating fact tables.
Limitations
Bridge tables are useful for connecting tables of similar size and complexity. You may run into some issues when trying to use a bridge table to relate tables with a huge disparity in size. For example, if the column you are trying to make a bridge table on includes one table that has 100 unique values and another table that only has five unique values, then you may want to reconsider using a bridge table for this many-to-many relationship.
Looking to expand your team’s knowledge in Power BI? Check out Data Coach’s Power BI courses for more in-depth tutorials.
