Merge Tables Power Bi

There are scenarios where different departments are working separately and sending data to a central location for consolidation. Organizations where data governance is taken seriously, there are less efforts required in combining such data. However, in scenarios where there is considerable variance/difference between source files, it is difficult to append or merge multiple files.

Merging tables in Power BI dataflows with PRO subscription Power BI dataflows is a powerful self-service ETL tool. However, some features demand the use of Power BI Premium, way more expensive than the PRO version and sometimes inaccessible. One of these tasks, for example, is the merge of two tables. Just because you can merge all the tables using Power Query, it doesn’t mean you should do it. The process of data modeling is not based on CAD DOs, it is based on SHOULD DOs. Power BI modeling starts with a pen and paper. This is very important that you start your modeling by a pen and paper. Yes, no tools or services needed. Just a pen and paper.

This blog post is sharing basic features available with Power BI to tackle such scenarios. In this example we will use a sample excel file having necessary tables involved. Download Sample Excel File

Appending Tables

For Example related to append, following tables will be used.

List A

CodeItemPrice
1Pencil2
2Eraser3
3Pen5
4Notebook11

List B

CodeItemPrice
1Pencil2
2Eraser3
5Marker12
6Whiteboard45

Step 1

Open a Power BI desktop file and click on “Get Data” icon from the ribbon. Select “Excel” and paste following URL in filename section.

Note: If you have downloaded this file, you can select file from your folder.

Step 2

Select checkboxes of “List A” and “List B” from the list and click “Edit”

Step 3

You will notice that Power BI Query Editor window will open after previous step.

Note: If for any reason this window is not opening directly, then you have to click on “Edit Query” icon on the ribbon.

Step 4

Right click on Query Pane and select as follows
New Query>Combine>Append Queries as New

Step 5

Append data in power bi

In order to append files, you need to select following files as mentioned below. Since in our example, we are using only two tables, multiple tables can be selected if second option “Three or more tables” is clicked. Press “OK” when correct files are selected.

Step 6

You will notice that now both tables are combined and showing collective list.

1Pencil2
2Eraser3
3Pen5
4Notebook11
1Pencil2
2Eraser3
5Marker12
6Whiteboard45

However this list has duplicate values. These were purposely entered in the list to show you how we can get rid of those.

Step 7

Click on “Remove Rows” icon from the ribbon and select “Remove Duplicates”. This action will give you final list with unique items from both lists.

This will complete your append operation.

Final list will be like below.

1Pencil2
2Eraser3
3Pen5
4Notebook11
5Marker12
6Whiteboard45

Step 8

You can rename new combined table as “Combined_Appended” for this example.

Step 9

Click on “Close and Apply” icon on the ribbon to complete this activity.

Merging Tables

Merging of tables is required when you are combining different tables with some common columns to make use of required columns of other tables. This is helpful in situations where product specification is maintained in one table while product prices are maintained in another. Following tables are used to this scenario.

1- Combined_Appended

2- List C

To import List C table, follow similar steps like mentioned in Step 1 of previous example (Append Tables).

Step 2

Select checkbox of “List C” from the list and click “Edit”

Step 3

You will notice that Power BI Query Editor window will open after previous step.

Note: If for any reason this window is not opening directly, then you have to click on “Edit Query” icon on the ribbon.

Step 4

Right click on Query Pane and select as follows
New Query>Combine>Merge Queries as New

Step 5

Now select files as demonstrated below. After selecting files click on “Code” column in both tables. This will make selected columns gray to identify columns used for merging tables.

Now click on “OK” to confirm merging.

Step 6

Append Tables Power Bi

Once merged table is loaded, click on the icon on the top right corner as mentioned below to expand table columns.

Step 7

You will see following window like below after completing previous step.

Now select Columns not available in first table to avoid duplicates. I our case we will select only “Box” and “Units” columns. In this example we can deselect “Use original column name as prefix” checkbox.

Click on “OK” to complete this step.

Step 8

You will now see a merged table with combined columns like below.

Step 9

Click on “Close and Apply” icon on the ribbon to complete this activity.

I have done a few videos on YouTube explaining how to join tables using Power Query or DAX. If you follow the channel, you probably have seen the videos and this blog post will serve as a compilation of all the material.

However, if you are new, this will serve as a tutorial for beginners on how to joins in Power BI. Either way, I believe this post will be useful for all, so lets get started.

Joining tables in Power BI

If you want to join tables using power query there are a few options:

  1. Left Outer
  2. Right Outer
  3. Full Outer
  4. Inner
  5. Left Anti
  6. Right Anti

It doesn’t say much right? What are all those joins?

Before jumping into how to do joins in Power BI, it is worth to take a few minutes to explain what those joins are with an example.

If you prefer a video, watch the video below otherwise continue reading:

An example on how to join tables

Let’s imagine that we have a bike company and we have in our data warehouse two tables: One with a list of products and price and another one with a list of products that we have in stock. We have in stock more products than those we manufactured ourselves and this list contains the entire list of products in stock. Here is how the tables look like:

The green rows represent the rows that match on both tables.

Let’s join those tables using the different combinations of joins available in Power BI:

Left Outer join

Let’s say that somebody in manufacturing wants to know which bikes we have in stock. In that case we would do a left outer.

When we do a Left Outer, we are taking all the rows from A and the matching ones from table B. Left outer will return from table B only the products that are present in table A.

Right Outer Join

Now, somebody working at the warehouse wants to know which products we manufacture ourselves. In that case we would do a right outer.

When we do a Right Outer, we are taking all the rows from table B and the matching ones from table A. Right outer will return from table A only the products that are present in table B.

Full outer Join

Product management department asked you for a list of all products available for sale. In this case, you will do a Full Outer.

When we do a Full Outer, we are taking all the rows from table A and all rows from table B. Full outer will return a table with all records, matching the ones that are available on both tables.

Inner Join

The planning department asked you for a list of products that are in stock. They don’t want to see any other products as they are not supposed to be in stock. In this case, you will do a Inner join.

When we do a Inner join, we are taking only the matching rows from table A and table B. Inner join will return a table with all matching records, excluding everything else.

Left Anti Join

Product management called you again, this time they want a list of products that are not in stock to review their strategy. No problem, in this case Left Anti is all you need.

When we do a Left Anti, we are taking all the rows from A that do not have a match in table B. Left anti will return all rows from table A that do not have a match on table B.

Right Anti Join

The logistics department want a list of products that are in stock but we don’t manufacture ourselves.. This time, Right Anti will do it.

When we do a Right Anti, we are taking all the rows from B that do not have a match in table A. Right anti will return all rows from table B that do not have a match on table A.

Easy peasy, right? 🙂

I have actually created a guide to remember all this joins:

and you can download it here.

Joining tables in Power Query

So now that you know what the different joins are, lets see how to do them in Power Query in this video:

Keynotes:

Left Outer Join 01:37

Right Outer Join 02:51

Merge Tables In Power Bi

Full Outer Join 03:20

Inner Join 03:39

Left Anti Join 03:52

Right Anti Join 04:17

Merge calculated tables power bi

Join tables with DAX

Ok, so now that we are experts on joins, let’s try to join tables using DAX.

You can do the same types of joins in DAX as you do in Power BI. There are many ways to do it, and I will show you now some examples of DAX functions that will allow you to join tables.

I dont have just one video for this, but one video per function as this is part of my DAX Fridays series, but I will put a link here so you have access to all of them in one place.

But first of all, what function to use for what? Here it is:

Merge
  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

I have created the same visual but with DAX functions:

and it is included in the same guide.

Tutorial with example

With generate, you can do an inner join and outer join of tables using DAX:

GENERATE, GENERATEALL:

NATURALINNERJOIN, NATURALLEFTOUTERJOIN:

04:35 Innerjoin
07:59 Outer join

More tutorials on Joining tables: CROSSJOIN

In this video I will show you how to do an anti join of tables using DAX:

EXCEPT:

Download example files:

To download the files from our download center click *HERE*.

Joins mouse mat

If you enjoy the tutorial, you might want my mouse mat: