Table Relationships based on multiple columns

[Anonymous]

Is there any way to create a table relationship that correlates 2+ columns instead of 1? I don't have a unique column to join the tables but by comparing multiple columns then the data could be correlated. 

[Anonymous · Accepted solution]

Hi  @Anonymous ,

You can combine two columns into one column, join using that column

Go to Power query and find the two columns you need in Table 1 – Merge Columns.

Repeat the first step for Table2.

Then use the merge column to join.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[abuitink]

when merging 2 or columns you lose your original single column(s) which is annoying, what tips do you have? 

[IgorKuzenkov]

You can duplicate all your columns then do Merge on duplicated ones. So, the original columns were left, plus, you get new column to use in your multiple column relationship. Do the same in both related tables.

[DataInsights]

@Anonymous,

You can create a column that concatenates the columns that should be used in the relationship. Then, create a relationship using the concatenated column.

[S19K15]

What about performance? What about when there is the need for three columns? This is very annoying. Normally it should be like analysis services and send the respective query to database to take the advantage of indexes.