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