cancel
Showing results for 
Search instead for 
Did you mean: 
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

24 REPLIES 24
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."

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.

RobinDeFal
Resolver II
Resolver II

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

@RobinDeFal  - 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!

MatjoCol
Frequent Visitor

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

@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?

 

What if in the two columns merged there are values 1 and 11, and the same in the columns merged in the second table? There will be an incorrect join for 1-11 and 11-1.

You can do this in Power Query with Table.Join or Table.NestedJoin; as they are, these functions will join on a single column only; you can circumvent this, by replacing the Table parameters with Table.AddColumn, which can add - on the fly - a new, composite column, to join on, something like:

 

JoinedTable = Table.NestedJoin(Table.AddColumn(Table1, "CompositeColumn1", each [JoinColumn1] & [JoinColumn2]), "CompositeColumn1", Table.AddColumn(Table2, "CompositeColumn2", each [JoinColumn1] & [JoinColumn2]), "CompositeColumn2", "NewCompositeColumn"),

 Hi @LON  Thanks for the feedback, must admit I'd forgotten all about this, mainly because I'd worked around it, doing something similar to what you describe, using a DAX formula.

 

Regards

Fred

What an excellent idea!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors