Basic of Append Query in #PowerQuery

With no doubt, combining multiple tables (mostly on different worksheets, or even in different files) into a single “master” table for further analyses is one of the most tedious tasks we deal with Excel day to day.  Inevitably, manual Copy and Paste is the go-to option (unless you are VBA expert).   Sometimes, the tables we are trying to combine contain different columns.  You know that feeling of frustration, don’t you?

With Power Query, we can say NO MORE to copy and paste for this tedious task.  In this post, I am going to show you what Append Query does, and some interesting notes of it.

I will demonstrate how Power Query appends tables for four different cases:  

Case 1 – Appending tables of same columns in same order

Case 2 – Appending tables of same columns in different order

Case 3 – Appending tables with different columns

Case 4 – Appending tables of same columns in same order but headers of different CASE

For demonstration purpose, the tables used are very simple.  Since you know the basic, you should be able to explore more and go farther.

You may download a Sample File to follow along.

Note: All screenshots are prepared using Excel 365, which Power Query is renamed as Get and Transform, under the Data tab.  If you are using Power Query for Excel 2010 or 2013, you may find the icons at different locations on your Ribbon, where Power Query has its own tab.  Nevertheless, the flow should be more or less the same.  I expect you have basic experience with Power Query interface to proceed.

Case 1 – Appending tables of same columns in same order

This is the most basic scenario.  We have multiple tables of exactly the same structure.

To do it with Power Query,

First, load all Excel Tables into the Power Query Editor:

1. Select any cell in the Excel Table
2. Go to Data tab
3. From Table/Range

(Note: All tables have been converted to Excel Table with names from Table1, Table2, etc…)

Once the Power Query Editor opens, we may perform various steps to the data loaded.  Nevertheless, for the simple dataset we have here, we can load the data to “Connection”:

Repeat the above steps to load the rest of Excel Tables in the workbook.

Now we are ready to Append queries.

To open Power Query Editor,

Double-click Table1 (or any query) on the Queries & Connections pane on the right.

1. Go to Home tab of the Power Query Editor
2. Append Queries
3. Append Queries as New

Select “Three or more tables”

Add the Tables we need to “Tables to append”

Tip: Hold Ctrl key to select multiple table under “Available table(s)”

OK ==> The three tables append together

Let’s rename the query to Case1

Load it to worksheet as Table

Here’s the result

Easy and straightforward.

Special note:

Append, by its names, append tables one by one.  In our example, Table 2 was appended to Table1; then Table 3 was appended to Table1 + Table2, in a sequence that was determined when we moved tables to “Tables to append” .  In this process, no aggregation is performed.

See below screen-cast for a better illustration, where Table 3 contains exactly the same items A, B, C:

IMPORTANT: Maybe you would expect the value of A, B, C in Table 3 will be “added” as a result, but this is not Append Query does.  That aggregation can be done by “Group by”.

Case 2 – Appending tables of same columns in different order

If you have read tutorial or blogpost about combining tables with Power Query, you may have a perception that all tables should have consistent structures – same number of columns, same headers, and in same order.  But indeed it is not the case!

Let’s append Table4 to Table6:

Let’s rename the resulting query as Case2

Load it to worksheet as Table

Here’s the result

See!?  The three tables appended correctly, regardless of the sequence of the columns in different Tables, as long as they carry exactly the same headers.

Isn’t it amazing?

Case 3 – Appending tables with different columns

Well, sequence doesn’t matter.  That’s great.  How about when we have inconsistent columns across tables?

Let’s append Table7 to Table9

Rename the resulting query to Case3

Load the result to worksheet as Table

Here’s the result

See!? Power Query detects different columns in Table9 and add those columns automatically when it appended to other tables, while unavailable data will be left blank.

What a hassle-free experience!

Case 4 – Appending tables of same columns in same order but different CASE

Last but not least, Power Query is very sensitive… to case.

While most people consider the headers of Table10 and Table11 are the same, Power Query does not.

Let’s see what happens when these two tables append…

Power Query gives extra columns as they are different columns in Power Query’s eyes.

Here’s the result

That’s why we need to be very careful to letter case, not only for inputting formula, but also for headers of all queries to be appended.

Tip: We may fix this problem by renaming headers in each related query to ensure they carry exactly the same headers.

After this blogpost, I understand the English word “Append” better.  😛