Appending Tables

Quick reference

• 00:04 One of the things that Excel is very good at is extending tables horizontally with
• 00:08 formulas but not so much vertically.
• 00:10 So if you want to stack January and
• 00:12 February and March tables into one big tall table for a pivot table,
• 00:16 Excel doesn't really have a good facility for doing this.
• 00:19 But the good news is the Power Query does.
• 00:21 Now the reality is though, we've already got the data in these tables.
• 00:24 So I don't want to go and create new queries that actually load data into new
• 00:28 tables in order to work with them, because there's no point in that.
• 00:32 So what I want to do is I want a query that points to this but
• 00:35 doesn't actually load anywhere.
• 00:37 And as you can see, right now, I have no queries at all.
• 00:39 So I'm going to set up three individual queries that point to these tables.
• 00:42 So the first one, I'm going to go into my Gen Table here,
• 00:45 I'm going to choose to get data from table range that'll pull it into Power Query.
• 00:49 And the only thing I'm going to do to this is change my column from date,
• 00:54 to a date datatype to drop these times off it.
• 00:58 Now what I want to do is I want to load this but
• 01:00 I don't want to create a new copy.
• 01:01 So I'm going to go to Close and Load, Close and Load Two.
• 01:05 And what I'm going to do is I'm going to choose rather than load this data
• 01:09 to a table on a new worksheet duplicating it,
• 01:12 I'm going to choose to only create connection.
• 01:15 I'm going to say, OK.
• 01:16 This is what I call a staging query where we actually just point to the data.
• 01:19 So I can mouse over this you can see that it's pulling back that data range and yet
• 01:23 it's loading this connection only it's not loading to a worksheet anyway,
• 01:27 because I already have the data.
• 01:28 So let me do the same thing for February, so we're going to get data.
• 01:33 We're going to go in and change the column to just a plain old date data type and
• 01:37 drop these times they're just not necessary.
• 01:40 Close them Load Two, and we're going to load this one once again,
• 01:44 to connection only.
• 01:46 So only create connection and say, OK, and finally, the last one.
• 01:51 Right click, we'll get data from table range, and
• 01:55 once again, change this to be a date.
• 01:58 There we are, and now Close and Load Two.
• 02:02 And once again, we'll load this one as well as a connection only staging query.
• 02:09 And there we are.
• 02:10 So, now in the queries pane we can see we've got a pointer to January we have
• 02:14 a pointer to February's data and March's data, but
• 02:17 we didn't actually duplicate any of our data at all.
• 02:20 Now, what I want to do at this point is I want to go and
• 02:23 I want to stack January and February together.
• 02:27 So the easiest way to do this is to come to January and
• 02:31 just right click on it and choose to Append.
• 02:35 And what will happen now is it says, all right, well,
• 02:37 what table would you like to append?
• 02:39 What do you want to start with?
• 02:40 I'm going to say January.
• 02:41 And then says, what would you like for your second table?
• 02:43 Interestingly enough, you can append it to itself.
• 02:45 There's not a lot of reasons to do this, but sometimes there might be one.
• 02:48 So, in this case though,
• 02:50 what I'm really interested in is I'm interested in February.
• 02:53 So I'm going to say let's append January to February and we'll say, OK.
• 02:57 It will launch us into Power Query, and you'll notice in the source step here,
• 03:01 you'll see that we have table combined Jan and Feb.
• 03:04 And what you can see is that we've got our January data showing here and
• 03:07 our February data showing down below.
• 03:09 So that's actually pretty cool.
• 03:10 It's called Append1, I'm just going to rename this as Transactions.
• 03:15 And this time I am going to load this to a table.
• 03:19 So we're going to go Close & Load Two, and I'm going to put it on a table,
• 03:23 and I'm going to drop this into the existing worksheet,
• 03:26 we're going to put it right here, and we'll say, OK.
• 03:29 And what you can see now is that we get both our January and
• 03:31 our February data in one table.
• 03:33 So that's pretty cool.
• 03:34 Now I want to get March.
• 03:38 So what would you do?
• 03:39 Now what a lot of people will do, is they'll do this they'll go right click on
• 03:43 Transactions and they'll choose to append.
• 03:45 And then say, okay, well, I've got Transactions, should I append January?
• 03:48 No, I already got that, February already got it, I'll grab March.
• 03:52 And when they say, OK, here's the challenge is you then no need
• 03:56 to define the name for your new output table.
• 03:59 And this isn't ideal because if we have our pivot table logic built against
• 04:04 Transactions, it's not going to get the March data,
• 04:07 because transactions is just January and February.
• 04:11 So what I'm going to do is I'm actually going to go and
• 04:14 delete this query here, we'll get rid of it, let's say delete.
• 04:17 Instead what I've actually done here is I've come into Power Query, and
• 04:21 you can do this just by editing the original Transactions query.
• 04:24 What I'm going to do is I'm now going to come up to the top here on the Home tab in
• 04:27 Power Query and we can choose append queries here.
• 04:30 And what you'll see is that I can now append March as well, and say, OK.
• 04:35 And at this point I get a new step that says that it's appended query and
• 04:39 we now have March in play here.
• 04:40 So now, if I hit Close and Load, we should see that this will reload and
• 04:45 also have March's data in there as well.
• 04:48 Although for some reason it doesn't seem to have reloaded, so you know what?
• 04:51 There we are, perfect.
• 04:52 If it didn't reload, of course I would just hit the refresh button and
• 04:56 that would actually force it to do so.
• 04:58 Now, of course, you may have noticed when we're doing our original append,
• 05:01 it gave you the option to choose three or more tables.
• 05:04 The scenario that I'm working with here is where we're slowly adding data and
• 05:07 this is why we're going in an appending individual steps along the way.
• 05:10 But this is a great feature that you can make use of in order to be able to go and
• 05:14 maintain your data tables individually.
• 05:16 And then get one big tall table all set up ready to go and
• 05:19 pivot to compare different months against each other.
• 05:22 Now I should let you know that this is the last video on Power Query in
• 05:25 this course but we have only just scratched the surface.
• 05:28 If you're really intrigued to see all the amazing things that Power Query can do,
• 05:32 you should definitely check out the Power Query course that is in the GoSkills
• 05:36 catalog.
• 05:36 I think you're going to find that it really helps you up your Excel
• 05:39 game even more.

Lesson notes are only available for subscribers.