Join Datasets with multiple columns conditions in Power BI

In Power BI you can create relationship between tables but there is some limitation with Power BI relationship, like you cannot create relationship(or join two tables) with multiple columns.

But in Power Query you can join tables with multiple columns conditions, under merge queries you can perform with all joins, so lets get started-

Dataset sample as below:

Table 1: UserDetail

CustId	Dept	Customer	City	Country
1	User	Annette Roulet	London	UK
2	User	Antonio Moreno	Toulouse	France
3	IT	Aria Cruz	Mexico	Mexico
4	Admin	Dinesh Raturi	Haridwar	India

Table-2: UserAmount

ID	User	City	Amount
1	Annette Roulet	London	100
2	Annette Roulet	Mexico	200
3	Antonio Moreno	Toulouse	200
4	Bernardo Batista	Mexico	400
5	Dinesh Raturi	Haridwar	100

Now follow these steps

Step-1: Click on Transform data

Step-2: The “Power Query Editor” window will open. Now, under the “Home” tab, click on “Merge Queries as New.”

“Merge Queries as New” will generate the joined output as a new dataset.

Step-3: Now, we will join two tables with multiple column conditions. You can select multiple columns by using Ctrl + Click.

Main steps:

• Choose the ‘UserDetail’ table from the first dropdown and select the ‘Customer’ and ‘City’ columns by using Ctrl + Click.
• Select the ‘UserAmount’ table from the second dropdown and choose the ‘User’ and ‘City’ columns using Ctrl + Click.
• In the ‘Join kind’ section, select ‘Inner Join.’
• Click ‘OK.’

Ensure that you have followed the same sequence of column selection for both tables. In other words, if you intend to join tables based on a condition like-

If you select “UserDetail (customer)” with “UserAmount (User)” and “UserDetail (City)” with “UserAmount (City),” then the column selection sequence must be consistent.

For example, if you choose “Customer” first and then “City” for the first table, you should follow the same sequence for the second table by selecting “User” first and then “City.”

This consistent column selection sequence is crucial because Power Query relies on this sequence to create the join condition and retrieve the data accurately based on your selections.

Step-4: A new dataset has been created. Now, click on the expand icon, as shown in the screenshot below, and select the column you want to add to the table. Then, click on OK.

Step-5: Now you can view the final output of the join with multiple column conditions.

Refer Power BI other post: Power BI tutorials

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.