The append operation in Power Query creates a single table by adding the contents of one or more tables to another, and aggregates the column headers from the tables to create the schema for the new table.
As you can see in below diagram there are two tables as Table A and Table B, and both tables have same columns after appending the tables, the resulting tables combines the both tables records.
In case, when tables that do not have the same column headers are appended then all column headers from all tables are appended to the resulting table. If one of the appended tables does not have a column header from other tables, the resulting table shows null values in the respective column, as shown in the below diagram in columns Code and Address.
Lets see the Append Queries operation in Power Query.
Here we have three tables named as TableA, TableB, and TableC as shown below.
Copy tables for practice:
TableA
| Id | Name | Code |
| 1 | Pradeep Raturi | XVCV56 |
| 2 | Rajesh Singh | VSFE47 |
| 3 | Amit Singh | GSWH5 |
| 4 | Suraj Rawat | BDGRJ3 |
| 5 | Andrew Mac | GSFW3 |
| 6 | Mayank | BWY4 |
TableB
| Id | Name | Code |
| 7 | Rohit Agarwal | JKD37F |
| 8 | Sujit Negi | VBFT3 |
| 9 | Ankit Joshi | BNAT39 |
| 10 | Sumit M | SDF3V |
TableC
| Id | Name | Code | Address |
| 11 | Manish Kumar | VSG46 | Delhi, old street road |
| 12 | Ram Prashad | DRTY3 | Phase -4, Building number 3 |
| 13 | Sujoy Gosh | NMS63 | Block -K, Apartment Number 457 |
Now, lets go to Power Query Editor, there you can find the Append queries operations on the Home tab in the Combine group. On the drop-down box you will see two options as follows:
- Append queries: it displays the Append dialog box to add additional tables to the current query.
- Append queries as new: it displays the Append dialog box to create a new query by appending multiple tables.
Lets see each operation one by one.
Append Queries
It appends an additional tables to the current query or you can say current table.
Lets select Append Queries from drop down as shown below.
Once you click on Append Queries, a Append dialog box opens.
It allows you to add additional tables to the current query, means you can add an additional table into a TableA.
You can see, Table to append drop down box, a current table is TableA. Lets append a TableB into TableA.
[Also read: Merge Queries in Power BI]
To append TableB into TableA, just select TableB from dropdown and click on OK button.
Power Query performs the append operation based on the names of the column headers found on both tables, and not based on their relative position in the headers sections of their respective tables.
You can see, TableB are appended, or combined into TableA, and TableB records can also be seen in TableA.
Also Power Query adds a transform step as Appended query in Query settings under Applied Steps section.
So far, we have seen how to append two tables. Lets see how to append three and more tables.
Before moving further, lets remove the TableB records from TableA for that just remove the transformation step Appended Query from Query Settings.
Once you remove the transformation step, you can see TableB records has been removed from TableA.
Lets append three and more table, assume that this time you want to append not only TableB into TableA but also one more table that is TableC.
For this, you need to select Append Queries from drop-down box.
After that now select Three or more tables option from Append dialog box as shown below.
Also you can see the current table is TableA, so all the table that you select will be appended into TableA.
To append tables, In the Available tables list, just select each table that you want to append, and then select Add. After that all the tables you want appear in the Tables to append list.
Lets select TableB, and TableC (select multiple tables with CTRL keyword)Â and click on Add button.
You can see, once you click on Add>> button, TableB, and TableC appears in the Tables to append list.
In case, if you want to remove the tables from Tables to append list then you can select table and remove by clicking on remove icon (X) as shown below.
Here we do not want to remove any table from append list so will not click on remove icon.
Now to append these tables into TableA, just click on OK button.
Once you click on OK button, you can see, TableB and TableC are appended into TableA.
Now TableA combines records of both tables, you can also see that TableA and TableB do not have address column, but tableC has an address column so after appending these tables the resulting table that is TableA will have an Address column.
It displays null values in address column for TableA, and TableB as these table do not have an address column.
Append queries as new
Append queries as new operation creates a new query by appending multiple tables.
Lets select see the Append queries as new operation, before this just remove the transformation step from Query settings to remove the TableB and TableC records from TableA.
Once you remove the step, you will get back the original TableA as TableB and TableC records are removed from tableA.
Lets go to Append Queries drop-down and select Append queries as new.
Once you click on OK button, you will see a new table or you can say a new query named Append1 is created which appends all tables TableA and TableB.
You can rename the new table, by giving it meaningful name, lets rename it to TableAB.
Also Read..
Pivot columns – turn rows into columns
