Locked lesson.
About this lesson
This lesson explores connecting to an external workbook to append all tables, worksheets and/or ranges within it.
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.
Append objects in an external Excel workbook.xlsx19.5 KB Append objects in an external Excel workbook - Completed.xlsx
172.4 KB
Quick reference
Append Objects in an External Excel Workbook
An overview of appending multiple objects in another Excel workbook.
When to use
When you have multiple objects (Tables, Named Ranges or Worksheets) in another Excel file that you would like to combine into a single table.
Instructions
Getting started
- Create a new query --> From File --> From Excel Workbook --> browse for the file to import
- Select the folder (the root of the Excel file) and click Edit
- A table of available objects is created
Building the query
- Filter the Kind column to a single type of object (worksheet, named range or table)
- Filter out any objects that are not part of the append
- Remove any columns that are not needed
- Expand the column of available objects
- Apply transformations as required
- Set data type for each column
- Rename the query
- Close & Load the query
Hints & tips
- Connecting to the root file instead of the individual objects ensure that data from new objects gets added to the query when refreshed
- You CAN import these objects from an external workbook:
- Worksheets
- Tables
- Named Ranges
- You CANNOT import these objects from an external workbook:
- Dynamic Named Ranges
- When importing worksheets you may wish to prevent new columns from being added to your output. To do this, select the columns you need --> right click the column header --> Remove Other Columns
- 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.