Locked lesson.
About this lesson
Appending tables is the process of stacking them vertically to create long tables that can be used to feed business intelligence solutions and PivotTables.
Exercise files
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.
Appending tables manually.xlsx167.9 KB Appending tables manually - Completed.xlsx
343 KB
Quick reference
Appending Tables Manually
An overview of manually appending multiple tables in a query.
When to use
When you want to stack multiple tables one after the other into one single table, instead of copying and pasting the data to compile it.
Instructions
Create staging queries for the source data tables
For each data source
- Create a new query that points to the data source
- Perform whatever transformations are required to clean up the data
- Finalize the query by choosing Home --> Close & Load To… --> Only Create Connection
Append the staging queries
- Open the Queries pane in Excel
- Right click one of the tables you wish to append --> Append
- Choose the other table(s) you wish to append and click OK
- Rename the newly created query
- Define the data types for the columns
- Finalize the query by loading it to the desired destination
Appending more data to an existing query
- Open the Queries pane in Excel
- Select the original Append query (whatever it is now called) --> Edit
- Go to the Home tab --> Append
- Choose the additional table(s) to append
- Click Close & Load
Hints & tips
- To open the queries pane
- Excel 2016: Go to the Data tab and select “Show Queries” or “Queries & Connections”
- Excel 2010/2013: Go to the Power Query tab and select “Show Pane”
- Power BI: Click Edit Queries
- Tables may be appended to itself, if needed
- There is an Advanced option when doing the initial Append which will allow you to choose multiple tables to append
- 00:04 In this video, we're going to look at a pending table.
- 00:08 So that's stacking them vertically on top of each other.
- 00:11 This kind of replicates the copy and paste if you wanted to actually take lets say
- 00:15 your January transactions and append February to the bottom.
- 00:18 And then append March to the bottom as well.
- 00:21 You'll notice we have three tables here, I have a January table.
- 00:24 It is formatted as an official Excel table because the Table Tools,
- 00:28 design tab is coming up.
- 00:29 We can see here's February, and here is March.
- 00:33 But if I wanna base a pivot table off of this data, I really need it listed out in
- 00:37 one big long table, and right now it's separated into three individual ones.
- 00:42 So what we're gonna do is we're gonna use Power Query to append this.
- 00:45 Because that way if anything else gets added to the bottom of any of these
- 00:49 tables, we can just refresh it and it will work nicely.
- 00:51 To do that, we're gonna go and we're gonna create a new query from Table/Range.
- 00:56 But the thing is with this data, when we look at it, we go okay, well, you know
- 01:00 what, this is pretty easy to change up, I'm gonna go and create a query.
- 01:03 I'm gonna call this guy here just January, or just Jan for short.
- 01:08 I'm gonna go and change the date column,
- 01:10 instead of a date-time we're gonna force it to date.
- 01:13 And at this point, once I've replaced this, I'm pretty much done,
- 01:17 there's not really anything else I want to do here.
- 01:20 Now, this is great and everything else but I don't really want to load it
- 01:24 to the worksheet cuz I've already got a table there called January.
- 01:29 Now, how do we do that?
- 01:31 Well, we've actually got this option under the Close & Load.
- 01:34 If you click the bottom half, we can actually go into this Close & Load 2.
- 01:39 Close & Load 2 allows us different options depending on how current our Excel is.
- 01:45 But the key ones you're gonna be looking for is,
- 01:47 do you have the ability to load a table, or to only create connection?
- 01:51 I'm gonna choose, Only Create Connection.
- 01:54 And essentially what this does, is it creates a pointer
- 01:58 to the January table that I have over here, or Jan 2008.
- 02:03 The data has been reformatted, if I mouse over it, I can get a preview and
- 02:06 you can see the dates have been picked up and reformatted and whatnot.
- 02:09 If I'd renamed anything, that would be in there.
- 02:11 But it's not being called or loaded or consuming any space.
- 02:15 We call this a staging query, and this is important because as you can see here in
- 02:20 my queries and connections pane, which comes up through queries or connections.
- 02:26 Or in older versions of Excel it was called show pane or show queries.
- 02:31 This brings up this particular window, notice we only see one table listed,
- 02:35 and that's the January table.
- 02:37 I'm gonna go grab February now.
- 02:39 So I'm gonna flip over to February here.
- 02:41 We're gonna click inside and I'm gonna create a new query from Table Arrange.
- 02:47 At this point, I'm gonna do the same things as I did before.
- 02:50 Gonna drop the ear off this, we'll call it Feb.
- 02:53 I'm gonna go and change the date column to be just a date.
- 02:58 Replace that, and now I'm gonna say close and load 2.
- 03:05 And now we're gonna say only create connection and then say, okay.
- 03:10 And now I've got both January and February.
- 03:12 And now I want to append these two guys.
- 03:15 So what I'm gonna do is this, right click on January and I'm gonna choose append.
- 03:22 And at this point it will come back it'll ask me if I would like
- 03:25 to do two tables or more.
- 03:26 We're gonna pretend right now we only have January and February records.
- 03:30 Notice that I can actually append the table to itself if I want to.
- 03:34 >> Which is kind of an interesting thing,
- 03:35 you may have a need to do it one day, so it's nice to know that it's there.
- 03:38 But for the most part, we're probably not gonna do that.
- 03:41 I'm gonna choose February, we're gonna say OK, and
- 03:45 what we're gonna get is a nice new query called Append 1.
- 03:49 Now, I'm gonna relabel this guy here, I'm gonna call it something like Transactions.
- 03:54 The challenges I'm working with a preview with a 999 row so
- 03:58 I can't really see if all of the state has been properly appended.
- 04:01 So what I'm gonna do now is I'm gonna use the default behavior and
- 04:05 I'm going to close load this out to a new worksheet.
- 04:08 There's 3837 rows, and if I go in very quickly knock off a pivot table or
- 04:13 disco Table Top Design summarized with Pivot Table.
- 04:17 I'm gonna throw out on the same work sheet.
- 04:19 And I'm very quickly, just gonna whip up a little pivot table here,
- 04:23 that has the day on rows.
- 04:24 I'm gonna go and say, all right well its automatically grouped up by month end.
- 04:28 That's perfect, cuz that's all its in there.
- 04:30 I will throw an amount on our values, so here we go.
- 04:34 We now know that we have both months with our values.
- 04:38 Now that's great, and then March comes along.
- 04:41 So I'm gonna go to March and I'm gonna create another staging query.
- 04:45 So again, I'm in my table, I'm gonna go create a new query from Table Arrange.
- 04:50 And this guy, I'm going to do the same things I did before.
- 04:55 We'll give it a nicer name, with March and
- 04:59 we're gonna change this to be just the date.
- 05:03 And because I have this in a table in the worksheets I'm gonna do a Close and
- 05:10 Load 2, and choose to only create a connection.
- 05:15 Now I want to append it to the transactions query and
- 05:19 you're gonna be tempted to do this, right click, Append.
- 05:26 We'll grab March.
- 05:27 Notice that Transactions is now on the list, and we'll say OK.
- 05:32 But this will actually create a new query called a Append1.
- 05:35 So this isn't gonna work for me, I need to say, no, wait a minute, if I do that,
- 05:39 my pivot tables, I would have to rebuild them all.
- 05:41 So no, forget it, we're not doing this, we're gonna say discard,
- 05:45 it's definitely going to be your first temptation.
- 05:47 The key that I want you to do instead is to right click the transactions query and
- 05:52 say edit.
- 05:55 And when you do what you'll notice is we come back in here's our January and
- 05:59 February stuff.
- 06:00 And inside here there's a nice button for append queries on the hometown.
- 06:05 And we can append generation ever again to transactions that sounds like
- 06:10 a circular reference waiting to happen but can be done, or March.
- 06:15 So, I'll add March, it's appending that to the table.
- 06:19 Still can't tell in here, so I'll hit close load.
- 06:23 It's now gonna spin and load, you'll see that we've now got 6,084 rows and
- 06:28 sure as anything.
- 06:29 If I go to my data tab and refresh my pivot table,
- 06:31 you'll notice that I have all three pieces in here as well.
- 06:35 So this is how we can manually append tables and this works really well if your
- 06:39 data is growing at a slow pace or isn't commonly changed.
Lesson notes are only available for subscribers.