Create a relationship with multiple columns in Power BI

Create a relationship with multiple columns in Power BI

When building Power BI reports we often need to join two (or more) tables together, but what if the relationship is defined by two or more columns? Relationships in Power BI are limited to single columns, but whilst this seems like a major limitation there is actually a simple solution to create a relationship with multiple columns in Power BI.

To create a relationship with multiple columns in Power BI we simply need to create a new column by merging the required columns together. What’s more, if we use the same name in both queries Power BI will automatically create the relationship for us.

To do this, we open the Power Query Editor using the Transform Data button…

Either from the Get Data Navigator when adding the data sources:

Get Data Navigator

or from the ribbon:

Transform Data

For my example I have two queries; Job_Planning_Lines and Job_Task_Lines, and I want to create a relationship between them using the two columns Job_No and Job_Task_No:

Power Query Editor

For each query we select the column we want to include, hold down the CTRL key after selecting the first:

Power BI fields selected

Note: the order in which you select the fields will determine the order the values are displayed in the new column.

Now it’s decision time, do we want to create the new field and remove the original fields.. or do we want to keep the original fields? 

To create the new key field and remove the original fields we select Merge Columns from the Transform tab:

Transform - merge columns

To create the new column but retain the original columns in our dataset we must use the Merge Columns button on the Add Column tab:

Add Column - Merge Columns

Once we’ve selected the appropriate Merge Column button, Power BI will ask for a delimiter and a name for this new column:

merge columns

You can choose to add a separator or not, I’ve chosen the colon character above and I’ve named the new column JobNoJobTaskNo. Remember to use the same setting in both queries.

Once the new column has been created in both queries save the changes with the Close & Apply button on the Home tab:

Close and Apply

If we now view the data we can see the new column:

view new merged column

And the relationship has been automatically created by Power BI:

table relationship

Using the new column:

manage table relationship

That’s it!

1 thought on “Create a relationship with multiple columns in Power BI”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.