One of the greatest values of data visualization tools is to connect different types of data tables to calculate results, illustrate trends or discover abnormal values. The relationships between your tables are necessary to create these connections.
Relationships in Power BI
Relationships in Power BI have multiple configurations, including cardinality and cross-filter direction, which determine how the tables connect and interact with each other.
One by one (1: 1)
A one-to-one cardinality means that the related column in both tables has only one instance of each value.
Many to one (*: 1)
A many-to-one relationship means that a column in a table has only one instance of each unique value, but the related column in the other table has multiple instances of a particular value.
Many to many (*: *)
Power BI has released a many-to-many relationship functionality as part of the functionality of the composite models in the Power BI desktop. You can enable it by navigating to file> options and settings> options> preview features> and checking chomogeneous models. You need to restart Power BI after activating composite models.
So … would not the possibility of creating a many-to-many relationship mean that there is no need to build a bridge bridge?
Technically yes … but depending on the number of data sources you are connecting, the complexity of your data and other factors that you might want to build a bridge to ensure the accuracy of your relationships.
In some initial comparisons I found that many Power-to-Many connections from Power BI are apparently accurate. However, I have not yet made extensive comparisons of the accuracy of a bridge table compared to many-to-many relationships in more complex analyzes.
Cross filter directions
When creating a relationship, you can choose between a single cross-filter direction or both directions of the cross-filter. With both directions of the crossover filter, when you filter values in a table, the same filter will apply to the values in the other table if they are connected by a relationship, treating all aspects of the connected tables as if they were a single table .
Build a bridge table
A bridge table, also known as an associative entity table, is a way to create a many-to-many join by creating a table with a column that contains a unique instance of each unique value, which creates a bridge to merge two or more many columns together.
Step 1: Clean up your data
I can not stress enough how important data cleaning is before building a bridge.
If you take the time to clean up the original tables, when we create references in the next step, the references will be cleaned up because you have already cleaned up your data. It saves time to clean up everything before having to go back and try to clean multiple tables after making transformations and references.
However, how do you plan to connect your data correctly if you have mixed case URLs in one table and not the other? If some URLs have final bars and others do not? Maybe your table values have white spaces at the end and you would have no idea!
If you do not clean up your data, then you will not have reliable output. You will spend some time building a nice dashboard in reality it does not tell you anything because the data is not connected correctly.
So, before going to build a bridge, clean up your data. See our checklist for URL cleaning and our post on cleaning and data deduction in Power BI to make sure your tables are prepared before moving on to Step 2.
Remember that for this tutorial you will not have to deduplicate your data because we will create a deduplicated table to link your other tables.
In this example, I will compare our conversions from paid search terms and rankings for organic keywords to find opportunities to optimize organic pages and increase paid spend.
Step 2: Create references to the tables you are linking
Create a reference for each table you want to link by right-clicking on the query and selecting rReferences from the drop-down menu. (In my example, I will create a reference to my AdWords table and my organic leaderboard table).
Why a reference and not a duplicate? A query reference refers only to the query result, while a duplicate duplicates the query code, creating a new object in memory.
Step 3: Remove other columns
Now that you've created the references for both tables, you can remove all the other columns in the reference queries except the columns you will make as a bridge. (In this case, the keyword column in the query to query organic rankings and the search term column in the AdWords reference query).
Right-click on the column you want to keep and select remove other columns from the drop-down menu.
Your reference queries should now have only one column each: the columns that will be combined into a bridge.
Step 4: Update all column headings to the same value
The next step is to make sure that the single column in the reference table has the same header value. Our next step is the combination of all the tables. And if your columns have different headers, Power BI will assume that they are completely separate columns. This is case-sensitive!
For example, I will update my "search term" column in "keyword" to match my query on organic rankings. You can do this by double-clicking on the header and changing the value.
Step 4: Add all the references to the bridge table
Now you will choose which reference query will become your bridge. It does not matter which one you choose, but I like to rename it as a bridge to simplify things.
While your query bridge is selected, you will need to select add queries at the top right of the home section in the query editor.
In the app window, select whether you will add two tables or more than 3 tables: in this example we add only two tables and select the references you are adding. The references will all have one (2) after the name of the query, unless you have renamed them.
If you did everything right, you should still see only a single column in your bridge table. If you have not renamed the headings correctly you will have more columns in your bridge.
Step 5: deduplicate your bridge
Next we would like to deduplicate our bridge of combined keywords and search terms by selecting rissue lines> remove errors, remove blank lines, remove duplicates
You can also right-click on the reference query that did not become your bridge and deselect it enable the load. This removes the reference table from the query results available to the report builder. (You will not need it because all your data is in your bridge).
You can close the query editor and apply the changes.
Step 6: Create a relationship between the bridge and the data tables
The next step is to create a relationship between your tables and your bridge. To select manage relationshipsDepending on how you've called the headers, you may already see that Power BI is trying to recognize connections between tables and has already created some relationships.
If you see some relationships already created by the automatic detection of Power BI, be sure to click and change the direction of the crossover filter, if necessary, as it will automatically be set to single.
To create a new relationship, select new and then highlight the columns in each table that we should associate. Cardinality should be many to one (*: 1), because the search term column has many values and the bridge keyword column must have a unique univocal value to merge.
You can also view and create relationships in the relationships pane. A double-pointed arrow indicates a "both" cross-filter direction for a fast QA.
Now you are ready to build your dashboard with data connected!
When using a bridge, be sure to use the value of the bridge column when applicable (rather than data from one of the tables), because the bridge table must contain one of all the values of all the many tables.
For example, when I build my scatter chart to compare paid conversions with organic rankings, I will treat the keyword column from my bridge table.
Were made! We have created a relationship between our paid search terms and organic keywords. Now we can analyze which search terms are being converted and start digging the way we are classifying for the related organic keyword, then find opportunities to optimize or create new content.