Join tables based on multiple columns

[Anonymous]

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

account_id    experiment_id

Table 2

account_id   experiment_id

In sql I would do the following

select * from t1,t2 where t1.account_id=t2.account_id and t1.experiment_id=t2.experiment_id

How do I create such a relationship in dax? 

[alfmos]

What if in the two columns merged there are values 1 and 11, and the same in the columns merged in the second table? There will be an incorrect join for 1-11 and 11-1.

[JChaves]

This is new for me, and works perfectly!
Tks

[LON]

You can do this in Power Query with Table.Join or Table.NestedJoin; as they are, these functions will join on a single column only; you can circumvent this, by replacing the Table parameters with Table.AddColumn, which can add - on the fly - a new, composite column, to join on, something like:

JoinedTable = Table.NestedJoin(Table.AddColumn(Table1, "CompositeColumn1", each [JoinColumn1] & [JoinColumn2]), "CompositeColumn1", Table.AddColumn(Table2, "CompositeColumn2", each [JoinColumn1] & [JoinColumn2]), "CompositeColumn2", "NewCompositeColumn"),

[Anonymous]

 Hi @LON  Thanks for the feedback, must admit I'd forgotten all about this, mainly because I'd worked around it, doing something similar to what you describe, using a DAX formula.

Regards

Fred

[dchapman374]

What an excellent idea!

[bscholesboogie]

Unfortunately, this method doesn't work if your input tables are in DirectQuery mode.  When you try to merge, you'll get an error message and an option to transform the data input to Input mode.

[Anonymous]

So, new to Power BI but this is something I'm trying to achieve, using MSProject as input. How can I tell if the tables are "in DirectQuery mode" and how do you "duplicate" columns, is it simply a matter of adding a New Column and setting the formula to “DupCol = Col-I-want-to-join”

[Anonymous]

@TomMartens: I am now able to create a relationship between the 2 tables but somehow the filtering is still not working. Any ideas?

[gujames]

I'm having a similar issue.

I'm trying to merge together the tables so that Ican use two layers in a ArcGIS Map in PowerBI.

Data is broken out per the below:

Table 1: Brand Index

Col1 - Brand

Col2 - Index

Col3 - Latitude

Col4 - Longitude

Table 2: Brand Locations

Col1 - Brand

Col2 - Latitude

Col3 - Longitude

I'd like to merge the datasets so that the result shows:

Col1- Brand

Col2- Type (Index vs Location)

Col3- Index

Col4- Latitude

Col5- Longitude

But when I try to merge the datasets, or append them, I end up with two columns for Latitude and 2 columsn for Longitude. Any advise here would be greatly appreciated.