Creating a bridge table for many to many relations in Power BI

Bridge tables allow you to query many to many relations as a one to many one.

Open up power power query editor

On the home screen open up the power query editor by click transform data.

Create references from the tables you want to bridge.

Right click the table and select Reference for both tables.

Remove all other columns from the newly created referenced tables.

Right click the column you want the keep and select Remove all other columns from the drop-down for both tables.

Update the header values to be consistent on both tables.

Double click the header and change the values to match on both tables. They need to be the exact same names.

Append all references to the bridge table.

Rename one of the reference tables to bridge and then select Append Queries from the top right of your home menu.
You will get a popup allowing you to select the number of tables to append.

When your finished you should only see one column in your bridge table.

De-duplicate and clean up the bridge table.

Click on remove rows in the top menu and select all of the following: Remove Errors, Remove Blank Rows and Remove Duplicates.

Remove the remaining reference table from the report builder.

Right click the table en deselect: Enable Load.

After this step you can close your query editor and start creating the relations in the Dashboard.

Open up relationship window by clicking Manage Relationships in the top menu.

• Click New in the bottom of the pop-up.
• Select the first table and create a relationship on the appropriate field with many to one cardinality and both directions
• Select additional tables and create similar relationships.

Now your setup to start querying your bridged tables.