Difference Between Merge and Append in Power BI

Exploring the Differences Between Merge and Append

In Power BI development several times we have to combine two or more tables to get the insights required in a particular scenario.

To combine two tables we have many ways out of which Merge and Append are two ways which are used by Power BI development services very frequently and big reason for this is a dedicated button given on Power BI tool.

Before getting into the differences between Merge and Append let’s first explore how to merge and append works!

READ – Power BI Direct Lake Mode: A Complete Guide

Merge

In merge, we can combine two tables based on a common column present in both tables. We can compare this with the joins which we use to combine tables in databases.

To understand better let’s take an example –

Let’s assume we have to combine two tables –

Coaches

Athletes

As you can see we have two columns –

1) NOC
2) Discipline

These columns are common in both tables so if we have to apply the merge here we can do it this way –

1) First click on the Merge Queries icon you will get two options –

i)Merge Queries – Choose this option if you want to combine a table with an existing table.

ii)Merge Queries as New – Choose this if you want to create a new table by combining two tables.

For now, let’s choose the 2nd option –

2) You will get this popup page and now you have to choose the 2 tables you want to combine by clicking on the option indicated in the below image

3) After choosing the tables now we have to choose the common column present in both tables which we use to combine the tables in our case let’s choose NOC column as shown below –

4) Also choose the join which you want to apply as shown below and click ok –

After clicking ok you will get a Merge1 named New Table which contains columns from both the tables which will be our required table as shown below –

Append

Append also used to combine tables but in append, we can combine 2 or more than 2 tables which we can’t do in merge.

Also in append, we combine tables vertically (horizontally in merge) and to get the best result the two tables should have the same number of columns with similar data types.

To understand better let again take an example –

Let’s take the same example that we took in the merge but to get the best result I have removed an extra column (Event) from the coaches table which was not present in the Athletes table.

Coaches

Athletes

1) First click on the Append Queries icon you will get two options –

I) Append Queries – Choose this option if you want to combine a table with an existing table.

ii) Append Queries as New – Choose this if you want to create a new table by combining two or more tables.

Again for now let’s choose the 2nd option –

2) You will get this popup page if you choose the two-table option and you can choose the tables from the indicated options below

3) If you choose more than two tables then you will get the below popup and you can choose tables that you want to combine.

For now, let’s choose two table options and after selecting the 2nd table click ok –

After clicking on the ok button you will get a new table named Append1 as shown below and that will be your required table with data from both coaches and Athletes tables.

The main differences between Merge and Append in PowerBI

Sr. NO	Merge	Append
1	You need a common column in both tables to perform a merge with the same data available.	Duplication will be there if the data is present in both the tables in the case of append.
2	You don’t need a common column to perform a merge with the same data available.	Append can be performed between more than two tables.
3	The final table will be created by adding columns from both the tables.	The final table will be created by adding rows from both the tables.
4	we need to implement a join(out of 6) to perform the merge operation.	we don’t need to implement any join to perform the append operation.
5	We can perform a merge operation between two tables only.	Duplication will be there if the data present in both the tables in the case of append.
6	We can apply to merge to get data without duplication by applying proper join.	Append is faster than merge as there is no process of row matching.
7	The merge process is relatively slow from append as there is a process of row matching also based on applied join.	To get the best outcome in appending the column numbers should be the same.