Append Queries in Power Query Editor

We can combine two or more queries in Power Query Editors. The append queries is basically appending the rows from one table below another (i.e., vertical stacking).

Access Path:

Home > Append Queries

Options:

Append Queries: Displays the Append dialog box to add additional tables to the current query, which will add the rows from an existing table into another.
Append Queries as New: Displays the Append dialog box to create a new query by appending multiple tables, meaning the output will result in a new query or table.

Note:

The related column names of the appended queries must be the same; otherwise, new columns will be created in the resultant table.
If any column is not available in an appended table, the resulting rows from that table will show null values in the respective column.
Power Query performs the append operation based on the names of the column headers found on both tables, not their relative position.
The final table will have all columns from all tables appended.

The append operation requires at least two tables. The Append dialog box has two modes:

Two tables: Combine two table queries together. This mode is the default mode.
Three or more tables: Allows an arbitrary number of table queries to be combined.

Note: The tables will be appended in the order in which they're selected.

Step 1: Create First Dummy Table

Power Query M

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice", Sales = 100],
        [ID = 2, Name = "Bob", Sales = 200]
    })
in
    Table1

The output of the above code is a table named Table1 with two records: Alice (Sales = 100) and Bob (Sales = 200).

Step 2: Create Second Dummy Table

Power Query M

let
    Table2 = Table.FromRecords({
        [ID = 3, Name = "Charlie", Sales = 150],
        [ID = 4, Name = "David", Sales = 250]
    })
in
    Table2

The output of the above code is shown below:

Step 3: Select any table and then click on “Append Queries as New”.

In the Append dialog box. Select the tables and then click on Ok.

In the below image we can see that a new query is created.

The M function which is behind the UI's "Append Queries" is Table.Combine.

Syntax Table.Combine({Table1, Table2})

Table.Combine is used to append multiple tables with matching column names.

Appending Tables with Different Columns (Schema Mismatch)

If tables have different columns, Table.Combine fills missing columns with null.

Power Query M

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice", Sales = 100],
        [ID = 2, Name = "Bob", Sales = 200]
    }),
    Table2 = Table.FromRecords({
        [ID = 3, Name = "Charlie", Region = "North"],
        [ID = 4, Name = "David", Region = "South"]
    }),
    Combined = Table.Combine({Table1, Table2})
in
    Combined

The output of the above code is a table named Combined that merges the rows from Table1 and Table2. Missing columns will have null values.

Columns Sales and Region will be null where not applicable.

Previous Next