Append and Merge Tables in 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.

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

Code Item Price
1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11

List B

Code Item Price
1 Pencil 2
2 Eraser 3
5 Marker 12
6 Whiteboard 45

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.

https://ezpowerbi.files.wordpress.com/2018/07/ezpowerbi-com-merge-and-append-sample-data-012018.xlsx

Get Data

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

EZPowerBI.com Append Queries - 1

Step 5

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.

Append files

Step 6

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

1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11
1 Pencil 2
2 Eraser 3
5 Marker 12
6 Whiteboard 45

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.

Remove Duplicates

This will complete your append operation.

Final list will be like below.

1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11
5 Marker 12
6 Whiteboard 45

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).

Get Data

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

merge

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.

Merge File Selection

Now click on “OK” to confirm merging.

Step 6

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

expand

Step 7

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

Select Columns

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.

Select Columns2

Click on “OK” to complete this step.

Step 8

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

merged file final

Step 9

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