Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

 

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? 

1 ACCEPTED SOLUTION

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:

2017-10-13_22-27-14.png

 

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

29 REPLIES 29

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.

This is new for me, and works perfectly!
Tks

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"),

 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

What an excellent idea!

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.

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

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

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.