No more Copy and Paste to combine tables with #PowerQuery

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

Excel Tip - No more Copy and Paste with PQ_Case1

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

Excel Tip - No more Copy and Paste with PQ

(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”:

Excel Tip - No more Copy and Paste with PQ1.1

Excel Tip - No more Copy and Paste with PQ1.0

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

Excel Tip - No more Copy and Paste with PQ2.0

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

Excel Tip - No more Copy and Paste with PQ2.1

Select “Three or more tables”

Excel Tip - No more Copy and Paste with PQ2.2

Add the Tables we need to “Tables to append”

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

Excel Tip - No more Copy and Paste with PQ2.3

OK ==> The three tables append together

Let’s rename the query to Case1

Excel Tip - No more Copy and Paste with PQ2.4

Load it to worksheet as Table

Excel Tip - No more Copy and Paste with PQ2.5

Here’s the result

Excel Tip - No more Copy and Paste with PQ_Case1

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:

Excel Tips - Complicated vlookup with PQ.gif

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

Excel Tip - No more Copy and Paste with PQ_Case2.0

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:

Excel Tip - No more Copy and Paste with PQ3

Let’s rename the resulting query as Case2

Excel Tip - No more Copy and Paste with PQ3.0

Load it to worksheet as Table

Excel Tip - No more Copy and Paste with PQ3.1

Here’s the result

Excel Tip - No more Copy and Paste with PQ_Case2.1

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

Excel Tip - No more Copy and Paste with PQ_Case3

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

Let’s append Table7 to Table9

Excel Tip - No more Copy and Paste with PQ4.0

Rename the resulting query to Case3

Excel Tip - No more Copy and Paste with PQ4.1

Load the result to worksheet as Table

Excel Tip - No more Copy and Paste with PQ1.1

Here’s the result

Excel Tip - No more Copy and Paste with PQ_Case3.1

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.

Excel Tip - No more Copy and Paste with PQ_Case4.0

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…

Excel Tip - No more Copy and Paste with PQ5.0

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

Excel Tip - No more Copy and Paste with PQ5.1

Here’s the result

Excel Tip - No more Copy and Paste with PQ_Case4.1

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

Advertisements
This entry was posted in Power Query and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.