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