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

16 REPLIES 16
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"})

 

New Member

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

 

@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

Super User II
Super User II

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

View solution in original post

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!

Unfortunately, this method doesn't work if your input tables are in DirectQuery mode.  When you try to merge, you'll get an error message and an option to transform the data input to Input mode.

So, new to Power BI but this is something I'm trying to achieve, using MSProject as input. How can I tell if the tables are "in DirectQuery mode" and how do you "duplicate" columns, is it simply a matter of adding a New Column and setting the formula to “DupCol = Col-I-want-to-join”

Anonymous
Not applicable

@TomMartens: I am now able to create a relationship between the 2 tables but somehow the filtering is still not working. Any ideas?

I'm having a similar issue.

 

I'm trying to merge together the tables so that Ican use two layers in a ArcGIS Map in PowerBI.

 

Data is broken out per the below:

Table 1: Brand Index

Col1 - Brand

Col2 - Index

Col3 - Latitude

Col4 - Longitude

 

Table 2: Brand Locations

Col1 - Brand

Col2 - Latitude

Col3 - Longitude

 

I'd like to merge the datasets so that the result shows:

Col1- Brand

Col2- Type (Index vs Location)

Col3- Index

Col4- Latitude

Col5- Longitude

 

But when I try to merge the datasets, or append them, I end up with two columns for Latitude and 2 columsn for Longitude. Any advise here would be greatly appreciated.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors