Appending Tables Manually

Quick reference

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