Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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   acco...
Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    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:

     

    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

     

EduSurveys's avatar
EduSurveys
Advocate II
4 years ago

I got this to work by selecting the first join columns, then holding control to select the second join columns under "Merge Queries."

TomMartens's avatar
TomMartens
Super User
4 years ago

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