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

Relationships in Power BI have multiple configurations, including cardinality and cross-filter direction, which determine the way that your tables connect and interact with each other. Cardinality A one-to-one cardinality means that the related column in both tables has only one instance of each value. Combine Multiple Tables in Power BI Combine Multiple Tables in Power BI: In real-time, your data is in a normalized format, but in some situations, you might need the de-normalized data. In this situation, you can combine those tables using a query editor. Let me show you how to combine multiple tables in Power BI with an example.

Appending Tables

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

List A

Aug 28, 2020 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.

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

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

Power Bi Merge Two Tables Online

Snapchat dark mode iphone 7. 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

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.

Power Bi Merge Two Tables

Click on “OK” to complete this step.

Step 8

Power Bi Merge Two Tables In Google Docs

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

Power Bi How To Merge Two Tables

Step 9

Power Bi Merge Two Tables Abap Tutorial

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