There are three approaches to joining tables in Power BI.
On the relationships page
With Power Query
With DAX
1) Joining With the Relationships Page
The easiest way to join tables is to simply use the Relationships page in Power BI. If your table ID's have the same name across tables, the relationships will automatically be picked up.
If Power BI didn't pick up on the relationships, you can easily create one. To do so, click and drag the column name from one table over to the other table.
To edit any relationship, double-click on the relationship line. A new window will appear with several settings.
In the settings window, you have the option to choose the relationship type (Many to One, One to One, or One to Many). You can turn the relationship off too. This is useful if you want to utilize a relationship for specific calculations using DAX, but don't want the relationship to affect normal filter contexts in your visuals.
The cross-filter direction option defaults to "Single" and is the recommended option, but also has an option for "Both". Warning: Choosing both as your cross filter direction can have performance and logical consequences for your model. As such, you should be extra careful when using bi-directional relationships. For more detailed description of these option, check out the Microsoft documentation on joining tables.
The Alien says: It is possible to build your measures without relationships using a different model design. Check out my blog here to learn more about the Disconnected Model Paradigm.
- Using DAX with a Relationship -
If you want to bring one column over from another table, you can use the DAX formula RELATED() to create a calculated column. In the following image, I'm pulling over the Employee Name field into the Monthly Sales table. This is only possible with an active relationship, over a many to one relationship (or one to one). Here, there is one employee on the Employee table, and many employees on the Monthly Sales table.
Employee Name = Related(Employees[Name])
2) Joining With Power Query
You may want to join a table in the data prep stages before it hits the data model altogether. Joining a table with Power Query actually merges the tables together with any number columns you want to bring over.
From the Query Editor, right click on the left side and choose New Query -> Merge as New.
Choose the tables you want to merge, and select the corresponding parent key and foreign key columns. You can select the type of join as well; Left Outer, Right Outer, Full Outer, Inner, Left Anti and Right Anti. Explaining these join types is outside of this blog, but Reza Rad does a great job of breaking it out for you here: http://radacad.com/choose-the-right-merge-join-type-in-power-bi.
Alien Says: As of the October 2018 update, the Power BI team introduced a new type of join called "Fuzzy merge". This allows you to join on two text/string columns that don't quite match up exactly.
Once you select OK, a new query will appear with the selected tables merged. You'll need to expand the 'right' side of the table to see everything.
I want to aggregate my 'right' table values, so I'll click the "Aggregate" radio button and select "Sum of Amount".
And we result in the following table:
- What's the M code? -
To join any two tables, the function is Table.NestedJoin(). Example:
let
Source = Table.NestedJoin(
Left_Side_Query, {"Left_Key_Column"},
Right_Side_Query, {"Right_Key_Column"},
"New Column Name",
JoinKind.LeftOuter
)
in
SourceThen, to expand and aggregate, the function is Table.AggregateTableColumn(). Example:
let
Source = Table.AggregateTableColumn(
MergedTable,
"ColumnToExpand",
{
{"ColumnToAggregate", List.Sum, "New Column Name"}
//,{Additional columns can go here}
}
)
in
Source
3) Joining With DAX
Ok, finally on to the DAX method. It offers two types of join methods:
Natural Inner Join
= NATRUALINNERJOIN(leftTable, rightTable)
Natural Left Outer Join
= NATURALLEFTOUTERJOIN(leftTable, rightTable)
Before we dive into it, I did run into a couple error's the first time I attempted to join tables with DAX:
Error: The Column with the name 'EmployeeID' already exists in the 'Table' table.
By renaming one of my column names (the 'EmployeeID' column to 'EmployeeID_FK'), the error went away for me.
So, in order for the DAX to work, you need to already have a relationship defined on the data model (on the relationships page described above), AND the column names can't be the same.
These DAX functions create new tables, so you need to use the "New Table" function on the Modeling tab.
Each function requires just two parameters; the left side table and the right side table. You don't need to input column names because DAX is picking that up with the relationship defined in the data model (which is why you need to have a defined relationship to use these functions).
Natural Inner Join creates a table with only matching records:
Natural Left Outer Join creates a table with all records on the 'left' table with matching records on the 'right' table:
I would actually recommend avoiding the use of DAX to create joined tables like this if possible. Not only do you run into errors if the names are the same (which will be the case more than not), your dashboard visuals will most likely run slower. Power Query already has the ability to perform joins, and it's best practice to do these things in the data prep stage anyways. If you're interested in more ways to join using these DAX functions, check out SQBLI's blog post here:https://www.sqlbi.com/articles/using-join-functions-in-dax/
Those were three ways you can join tables in Power BI. I hope you learned something or got something valuable out of it. Let me know in the comments if you have any other tips for us!
If you're struggling staring out with DAX, check out my blog post here going over the things you MUST know: https://www.antaresanalytics.net/post/2018/10/04/basic-power-bi-dax-concepts-you-should-know.
