Append Objects in an External Excel Workbook

Quick reference

• 00:04 In this video, we'll answer the question, how do I go about appending either
• 00:10 tables or worksheets from another Excel file together into one big, tall table.
• 00:16 So the first thing we need to do is go and grab some data.
• 00:19 So we're gonna create a new query from File.
• 00:23 And we're gonna target the Excel workbook we're after.
• 00:26 What we're gonna do is we're gonna browse in to our GoSkills sample data.
• 00:31 We're gonna look into the appendix cell files folder and
• 00:33 bring out the Excel data file from that folder and we'll hit Import.
• 00:40 And you'll see that this file has actually got a lot of stuff going on inside it.
• 00:44 We've got a January, a February, a March table.
• 00:47 We've got a February, January, and a March worksheet.
• 00:50 There's an info worksheet.
• 00:51 There's a named range.
• 00:52 And there's also this thing called the print area as well.
• 00:55 So if I want to go and actually start combining multiple items,
• 00:59 I could check the multiple items checkbox.
• 01:02 But the problem is that that's gonna name things.
• 01:05 So what happens then when somebody adds April?
• 01:08 Well, if I haven't chosen it through that checkbox, it's not gonna get pulled in.
• 01:12 So that'll get missed, so we don't want that.
• 01:14 So I'm gonna uncheck this guy right now and instead of actually picking off
• 01:18 individual worksheets here, what I'm gonna do is this.
• 01:21 I'm actually going to grab the root of Excel file and I'm gonna click Edit.
• 01:28 And that's not the most intuitive thing but by actually grabbing the folder,
• 01:32 it brings in all of the Excel objects into a great, big list.
• 01:36 You'll notice that it's got a few things going on here.
• 01:39 We've got a name column, which gives us the name of the particular object,
• 01:44 worksheet file, name range that we're dealing with.
• 01:47 We've got the item piece as well.
• 01:50 Now, this happens to give the same information with the exception that
• 01:54 we've actually got something actually defined in the item name
• 01:57 to tell us a little bit more about it.
• 01:59 Regardless, I'm not super fast about the this one,
• 02:02 I don't really need to deal with it.
• 02:05 The key thing that I wanna tell you about this though,
• 02:08 when you open up an Excel file in this manner to reach in to the root of it,
• 02:14 the really important thing is what do I want.
• 02:19 And it comes down to this column right here.
• 02:23 Notice in this column I have four worksheets, three tables, and
• 02:26 two defined names.
• 02:28 And here's a thing.
• 02:29 The most important thing you can do when you actually connect to this route
• 02:33 is to filter to one kind and only one kind.
• 02:37 The reason being is because we know that every table lives inside a worksheet.
• 02:42 And those defined names could be cells within a table which live inside
• 02:47 the worksheet which means that if we don't filter to a specific kind,
• 02:51 we could at least duplicate, if not as much as triplicate, our data,
• 02:56 and we don't wanna do that.
• 02:58 So what I'm gonna do is I'm gonna filter this down now and say,
• 03:00 you know what, I don't want all of these, I only want tables.
• 03:06 And when I say OK, it now filters a list down nicely to just tables.
• 03:11 At this point, the item column, the kind column, and the hidden column,
• 03:16 these guys aren't really required anymore, I only need name and data.
• 03:20 So I'm gonna grab the name column, hold down my Ctrl key, grab the data column,
• 03:24 right-click, and I'm gonna say Remove Other Columns and
• 03:28 we'll just gonna get rid of those.
• 03:30 We'll now take a peek as to what kind of data we have by clicking
• 03:33 in a white space and notice that we don't have any names associated or
• 03:37 any dates associated with any of these guys here.
• 03:40 So we're gonna do the nice little name trick on replacing values.
• 03:44 And we're gonna replace the underscore with space one comma space,
• 03:50 allowing us to turn this into a date.
• 03:57 We'll go and say Transform.
• 04:00 Now that it's a date column, we can transform this into an end of month.
• 04:06 And at that point, we've got a beautiful month and
• 04:09 column that we can use when we expand our data.
• 04:16 Uncheck the Use original column name as prefix.
• 04:19 Say OK and boom, just like that, we've now got a beautiful table
• 04:23 that we can finish off by setting our data types and
• 04:27 landing into the Excel grid once we've given it a nicer name like Transactions.
• 04:36 And Home and close and load.
• 04:39 And now, we're laughing because anytime somebody goes and
• 04:42 adds a new table to the workbook that's called, say, April_2008 or
• 04:46 May_2008, when we hit refresh, it's just gonna bring that data in for us.
• 04:51 It'll run it through the same blender and we're good to go.
• 04:53 So that's kind of a nice piece around us is we're not
• 04:56 using hardcoded names that get stuck.
• 04:58 It's a dynamic solution that can be refreshed on a regular basis.

Lesson notes are only available for subscribers.