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