Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Join tables based on multiple columns

Hi,

 

I have two tables . PowerBI does not let me join these tables as they do have unique values in one of the columns. In SQL I would join these tables based on two columns. 

 

Table 1

 

account_id    experiment_id

 

 

Table 2

 

account_id   experiment_id

 

 

In sql I would do the following

 

select * from t1,t2 where t1.account_id=t2.account_id and t1.experiment_id=t2.experiment_id

 

How do I create such a relationship in dax? 

1 ACCEPTED SOLUTION

Hey,

 

in the query editor

  1. duplicate the columns that you want to use for the join
  2. mark the duplicated columns you want to use for the join, choose "Merge columns" from the context menu.

Here is a screenshot:

2017-10-13_22-27-14.png

 

Repeat this for the 2nd table.

 

Now you are able to use this column to create a relationship between both tables

 

Don't forget to "hide" this column from report view.

 

Hopefully this answers your question

 

Regards

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

29 REPLIES 29
EduSurveys
Advocate II
Advocate II

I got this to work by selecting the first join columns, then holding control to select the second join columns under "Merge Queries."

I just tried this, and it's amazing. Never would of guessed you can do multiple joins by holding control. Wish this was also available for relationships.

Hey,

 

please be aware that the hint from the original question, that one column does not contain unique values. This hint indicates that join between tables should happen in the Power BI dataset and not in Power Query.

 

Joining tables in the dataset means creating a relationship, relationships are important for the performance and filtering the data in the data model. A relationship between two tables of tables can only use one column

Joining tables inside Power Query means creating one table from two tables. This kind of join is called merging tables. A merge inside Power Query can be based on multiple columns between the two tables.

 

Each type of the above mentioned tables joins is pursuing a different goal.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

I should clarify that my response was related to the Merge Queries function in Power Query, not creating relationships in the data model after loading queries. I read the question as a SQL user where Join is the equivalent to Merge in Power Query. I had previously created concatenated columns like you suggested in your solution but didn't like creating a new unnecessary column. I didn't know that you could merge queries based on multiple like columns in Power Query, but you definitely can in SQL. And so I tried holding control when selecting the second ID column (e.g., Customer ID as the first column, Month as the second column) in the "Merge Queries" interface, and that worked to Merge the two tables together based on values from two columns. It achieves the same result as this:

 

JoinedData = Table.Join(AgeData, {"Customer ID","Month"}, OccupationData, {"Customer ID","Month"})

I agree that you would not try to achieve the desired result using DAX.

shinypurple
Frequent Visitor

This worked for me. Good tip - Thanks.

Anonymous
Not applicable

Hi All,

Just wondering if merging/concatenating columns is the only way to join tables based on multiple columns? 

 

As this creates additional columns which is slowing down and making my report heavier.

 

Any update please?

 

Thanks 

Robin

@Anonymous  - did you see the post from @MichaelHenry from 3/8/2020?  All you have to is pass arrays of column names for the key parameters like in the example @MichaelHenry provided:

 

JoinedData = Table.Join(AgeData, {"First","Last"}, OccupationData, {"First","Last"})

 

MichaelHenry
Advocate I
Advocate I

This thread is the first result that appears in Google when searching for "M join tables on multiple columns" so it needs to be kept up to date. The accepted answer is wrong - it is possible to join using multiple keys. I assume at the time of the original response (2017) this was not the case. (Yes I realise the original question was asking about DAX but the accepted response was in M and that is what I followed until it was pointed out to me that I could use multiple columns).

 

Here is an example demonstrating a join on multiple columns:

 

let
    AgeData = Table.FromRecords(
        {
            [First = "Casey", Last = "Holmes", Age = 20],
            [First = "Jon", Last = "Henry", Age = 28],
            [First = "Phyllis", Last = "Hassan", Age = 32],
            [First = "Dua", Last = "Myers", Age = 33],
            [First = "Cody", Last = "Anthony", Age = 40],
            [First = "Sebastian", Last = "Currie", Age = 40],
            [First = "Jaspal", Last = "Chadwick", Age = 41],
            [First = "Suhail", Last = "Ryder", Age = 42],
            [First = "Fabian", Last = "Maldonado", Age = 45],
            [First = "Yara", Last = "Ellison", Age = 58]
        }
    ),
    OccupationData = Table.FromRecords(
        {
            [First = "Cody", Last = "Anthony", Occupation = "Bricklayer"],
            [First = "Fabian", Last = "Maldonado", Occupation = "Business Consultant"],
            [First = "Dua", Last = "Myers", Occupation = "Clerical Assistant"],
            [First = "Phyllis", Last = "Hassan", Occupation = "Estate Agent"],
            [First = "Yara", Last = "Ellison", Occupation = "Interpreter"],
            [First = "Casey", Last = "Holmes", Occupation = "Museum Curator"],
            [First = "Jon", Last = "Henry", Occupation = "Nurse"],
            [First = "Suhail", Last = "Ryder", Occupation = "Police Officer"],
            [First = "Sebastian", Last = "Currie", Occupation = "Professor"],
            [First = "Jaspal", Last = "Chadwick", Occupation = "Refuse Collector"]
        }
    ),
    JoinedData = Table.Join(AgeData, {"First","Last"}, OccupationData, {"First","Last"})
in
    JoinedData

 

Hey @MichaelHenry ,

 

please be careful with stating the accepted answer is wrong.
The question was not asking for DAX, it was asking for a solution that could be used in the data model. Creating columns inside Power Query is recommend as all columns will be compressed. Calculated columns using DAX will not be compressed. For this reason I created the columns inside Power Query.

 

In my original post I mentioned, that the created columns can be used to create the relationship.

 

It's important to understand what is required

  • the creation of a relationship inside the data model, only one column can be used
  • merging tables, multiple columns can be used inside Power Query

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

I also came here from the google search results. I'm glad I scrolled down to this post. Hopefully my reply helps bump this for the Microsoft team or something 

@MichaelHenry  Thank you for posting this!  Did you find this in Microsoft's official documentation?  If so, would you be willing to post a link to the page?

 

Thanks!

You can acually do this in the merge query editor. If you press control while selecting columns there is a small digit indicating the order displayed for each column allowing you to join on as many columns as you want. 

/Cheers

TomMartens
Super User
Super User

Hey,

 

currently it's not possible to create a relationship in Power BI between tables that spans more than one column.

 

For this reason I concat both columns in the Query Editor in each table, and use this column to create the relationship.

 

Hopefully this gives you an idea

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens: Thanks for the reply. I did not understand it completely. Could you give me example and tell me how the output would look? Will the concatenated column have unique values?

 

Sorry I am new to this. 

Hey,

 

in the query editor

  1. duplicate the columns that you want to use for the join
  2. mark the duplicated columns you want to use for the join, choose "Merge columns" from the context menu.

Here is a screenshot:

2017-10-13_22-27-14.png

 

Repeat this for the 2nd table.

 

Now you are able to use this column to create a relationship between both tables

 

Don't forget to "hide" this column from report view.

 

Hopefully this answers your question

 

Regards

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

OMG this was the reason I advocated for PBI, and now it's no better than any other tool. Way to go MS, for taking away the one reason PBI stood apart.

This worked great for me, thank you!

Could you please confirm whether or not you can use these steps with a Calculated Column? It doesn't look like on my end, but maybe I'm missing a step.

@TomMartens 

Thanks for guiding.

Can you please tell me how will we design Incremental load with Merged Query.

In my case, I have to join tables with outer joins, I can't done it with concatenation, If I join the tables in merge query, than how I manage the Last_Updated_Date.

Should I create the Reference of tables and than merge the referenced queries using merge query and configure the incremental load on the original loaded tables? 

What is the correct way to do it?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.