Appending Tables

Quick reference

• 00:04 >> Another issue that we can run into with data is where we have transactions being
• 00:08 captured in separate tables like we have here with January, February, and March.
• 00:13 But I wanna serve this to a pivot table,
• 00:15 which means that I really need these stacked in one tall table.
• 00:19 Now this is not a big deal, I could copy and paste and them, but
• 00:23 the reality is I would prefer to have this a little bit more automated.
• 00:26 So what I am going to do is I'm going to actually get Power Query to append this
• 00:31 which essentially replicates the action of copy and pasting.
• 00:34 To do that, I need to start by saying From Table/Range on my January table
• 00:39 because unfortunately, Power Query doesn't know that these tables exist.
• 00:45 But the reality is, is that even though it pulls in these data and
• 00:49 I'm gonna make one small change of forcing the date column to a date
• 00:53 we'll replace the current data type there.
• 00:56 Even though we're making that change, the reality is I don't really want to
• 01:00 load this out to a table in Power Query, because I already have the data there.
• 01:05 So I'm gonna choose Close and Load To, and
• 01:07 I'm gonna choose to load this to a connection only.
• 01:10 And basically, what will happen here is that I now have a pointer showing up for
• 01:14 Jan that goes back to this individual table, and you can see as I mouse over it.
• 01:18 It actually shows me the converted data, which is good.
• 01:22 Now I'm going to grab February as well will do the exact same thing.
• 01:26 We'll pull February in, once we get the opportunity,
• 01:29 we're gonna change the data type to a date, and replace it.
• 01:35 That's just overriding the date time,
• 01:37 the type that was there before, and we'll go to Close and Load To.
• 01:41 And once again, I'll load this to only create Connection.
• 01:45 So I now have both Jan and
• 01:47 Feb tables with pointers from Power Query that I can work with.
• 01:51 Why is this important?
• 01:52 It's super important because of this.
• 01:54 I can now right-click on the January table, and I can use the Append command.
• 01:59 When I do this, it will come back and
• 02:01 say, all right, you'd like to take your primary table of January.
• 02:05 What table would you like to append to it?
• 02:06 So this basically means stick it to the bottom.
• 02:09 And I can go and actually append it to itself if I want to,
• 02:12 which will be a little weird in this case, but there are use cases for that.
• 02:15 But instead I want to append my February table.
• 02:19 And when I do this it will kick off a new query called Append1.
• 02:25 And as you can see, it stacked everything nicely on top of each other.
• 02:28 Now I'm gonna go give this a nicer name.
• 02:30 I'm gonna call this Transactions.
• 02:33 And now, I'm gonna land this table out to Excel
• 02:38 using the Close and Load To behavior again.
• 02:40 But this time, I'm gonna choose to load it to a table on the existing worksheet.
• 02:45 And we'll just go put in over here beside these guys and say, OK.
• 02:50 And what you'll be able to see, is that it now has all of our transactions.
• 02:54 And the cool thing here is, because it's based on a table,
• 02:58 if I add a new transaction here for 61580.
• 03:02 And I put that in department 250.
• 03:05 And maybe I use the same date setup, I'm gonna go with February 28th,
• 03:09 2018, and we'll put this one in.
• 03:11 I'm gonna put in a positive number this time,
• 03:14 everything else seems to be negative.
• 03:15 And now, if I come back over here and take a look at my data table, and
• 03:19 right click, and refresh, you'll notice that it pulls that information in as well.
• 03:23 So that's pretty nice.
• 03:25 But now I want to grab March, so I'm gonna go here.
• 03:28 What do I need to do?
• 03:29 Same thing, Data from Table Arrange.
• 03:33 We'll pull this in as well, we'll change the Date to a Date.
• 03:40 Replace the current, and once again, Close and Load To
• 03:46 to make that connection only query that isn't duplicating data in the workbook.
• 03:51 And now you're temptation is this, and I want you to think about this.
• 03:54 Let's pretend there's a pivot table based off of this table, and
• 03:57 we wanna get margin here as well.
• 03:59 Your natural instinct is to go to Transactions, and say Append,
• 04:03 and say let's go and add March's table.
• 04:08 Look at that, we can actually choose January, February,
• 04:10 all transactions as well, no need to do it here, but let's go with March and say, OK.
• 04:16 And what you'll see is that this creates a new query called Append1.
• 04:19 Now this is obviously not what we want at all.
• 04:22 We want to be able to append this directly into the transactions query.
• 04:26 So we're gonna actually close this, and say let's discard this, we don't need it.
• 04:31 Instead, what we actually want to do is,
• 04:33 we want to modify the transactions query to pull our information in.
• 04:37 So with the query pane showing, remember you can always show it through queries and
• 04:40 connections.
• 04:41 We're gonna double click to edit the transaction's query instead.
• 04:46 And once we're in the transactions query on the Home tab,
• 04:49 there's an Append Queries button.
• 04:52 And when I click on this, it will now let me upend the March transactions to it,
• 04:58 we'll say, OK.
• 05:00 You'll be able to see that they show up in here, Close and Load.
• 05:05 And now if I had a pivot table, I would just refresh it, and
• 05:08 it will be pointing back to this information.
• 05:10 So this is pretty nice because it's working differently than copy/paste, but
• 05:14 it allows us to amalgamate everything.
• 05:16 We never have to worry about missing rows on the copy, pasting on top of rows, or
• 05:20 anything like that.
• 05:21 And we now have a pretty refreshable solution that every time somebody
• 05:24 adds new data at the bottom of these tables we can refresh it,
• 05:27 and we'll just keep on pulling it in.
• 05:28 And again, allow us to reuse our pivot table logic.

Lesson notes are only available for subscribers.