Forum Discussion
Join tables based on multiple columns
- 8 years ago
Hey,
in the query editor
- duplicate the columns that you want to use for the join
- mark the duplicated columns you want to use for the join, choose "Merge columns" from the context menu.
Here is a screenshot:
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
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
- danobri6 years agoNew Member
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!
- MatjoCol6 years agoFrequent 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
- DataStraine5 years agoAdvocate I
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
- TomMartens4 years agoSuper User
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