Power BI: How to Join or Merge Tables

Joining two tables (and more) together is necessary in many cases for better data analysis. Power BI allows us many options to join two tables using Power Query Editor and DAX. Using DAX In Power Query Editor, we can use Merge Queries functionality to join tables. Using merge queries allows these types of joins as long as there is a join key:

1. Left outer join
2. Right outer join
3. Full outer join
4. Inner join
5. Left anti join
6. Right anti join

Here we have an example where we want to join the following two tables where the join key is cust_id = customer_id.

Table 1:

Table 2:

Join Two Tables In Power Query Editor

To open the Power Query Editor, go to Home > Transform Data.

Once in Power Query Editor, in Home tab, click on Merge Queries. Under here, you have two options:

1. Merge Queries – this will merge the columns of the 2nd table in the current table that you are on.
2. Merge Queries as New – this will create a new third table where table 1 and table 2 will be merged.

Once you select Merge Queries (or merge Queries as New), a new popup will open where you can choose the type of join for the two tables.

Different Types of Joins in Power BI

Here are the type of joins that you can use on your tables:

1. Left Outer – all from first table, and only matching rows from second
2. Right Outer – all rows from first table, and only matching rows from first table
3. Full Outer – all rows from both tables
4. Inner – only matching rows from first and second table
5. Left Anti – rows only in first table
6. Right Anti – rows only in second table

Depending on your use case, you can use the join that works best for your case.

Once you’ve completed the merge, the resulting table will look like this where the second table will show as below. To choose which rows from the second table you’d like added to the first table, click on the small icon in the top right corner.

You can select which of the columns to show by unselecting the ones you don’t want in the merged table. Here we deselected the customer_id column as we already have the cust_id so we only have the company column (called company.company_name)

If you want to combine more than two tables, simply follow the above steps to merge the resulting merged table above with the third table.

Join Two Tables Using DAX

Using DAX to combine tables is usually less efficient and makes your report slower, but it is still possible to do so. These are the functions that you can use to do joins in Power BI using DAX:

1. Left Outer: GENERATEALL, NATURALLEFTOUTERJOIN
2. Right Outer: GENERATEALL, NATURALLEFTOUTERJOIN
3. Full Outer: CROSSJOIN, GENERATE, GENERATEALL
4. Inner: GENERATE, NATURALINNERJOIN
5. Left Anti: EXCEPT
6. Right Anti: EXCEPT