Forum Discussion
Join tables based on multiple columns
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
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
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.
I just tried this, and it's amazing. Never would of guessed you can do multiple joins by holding control. Wish this was also available for relationships.