Append Objects Within the Current Excel Workbook

Quick reference

• 00:04 In this video,
• 00:05 we're gonna look at appending objects within the current Excel workbook.
• 00:10 But the key here that we wanna focus on is that we have
• 00:13 lots of different things in here, and what we really wanna do is append
• 00:17 some tables together into one big long solution.
• 00:20 Let's take a look at what we have first.
• 00:22 If we go to the Formulas stab and go to Name Manager,
• 00:26 you'll notice that we actually have three individual Excel tables,
• 00:29 we have a print area defined, and a named range.
• 00:33 Now, the named range is right here, a single cell, our print area
• 00:37 is actually showing up on this work sheet, and then the three individual tables.
• 00:42 But these guys also have a problem.
• 00:45 We have our table name Jan_2008, but notice there's no dates in it.
• 00:50 Feb_2008 also has no dates.
• 00:53 And March underscore to this name also has no dates in it.
• 00:57 What I'd like to do is bring all these guys in, append them all together, but
• 01:01 have dates there as well.
• 01:03 So how do we do it?
• 01:05 Well, the answer is we're gonna go and create a new blank query.
• 01:09 Unfortunately, there's no easy way to get into this from within the Excel interface.
• 01:15 But if we go into blank query, we're gonna enter a new formula in the formula bar, so
• 01:20 we go on up to the formula bar, go to the view tab and
• 01:23 check the formula bar check box.
• 01:25 And we'll say equals Excel with a capital E.
• 01:29 Current with a Capital C and Workbook with a capital W.
• 01:33 Everything else should be lowercase and
• 01:36 we'll open close parentheses at the end and hit Enter.
• 01:39 And what you'll notice is that this give us access to all of the objects that exist
• 01:43 inside the current Excel workbook.
• 01:46 We have rangDept.
• 01:47 If we click in the white space, we can see that single cell.
• 01:50 We've got the PrintArea.
• 01:52 Is defined for us as well, so we can see that that expands these cells here.
• 01:57 And we have the individual tables as well.
• 02:01 Now, I want to use the tables from January, February, March.
• 02:06 So I'm going to go to the name column here and
• 02:09 I'm gonna filter out the print area and filter out range department.
• 02:13 I will say OK.
• 02:14 I'm gonna play a clever trick here to try and get to a month end column.
• 02:21 You'll notice that I have an underscore in here which I'm going to replace.
• 02:26 So we'll say right-click, replace values and we're gonna replace the underscore.
• 02:31 Switches a shift dash.
• 02:33 And I'm gonna replace it with space one comma space.
• 02:37 And the reason is because when I say OK, this will make this look like a date.
• 02:43 If I tried to convert Jan_2008 to a date, it would fail, because it doesn't know
• 02:47 what day it is, and that's required for the date serial number.
• 02:51 But in this case, I can now go and say convert this to a date, and
• 02:55 it will convert it into a proper date for me.
• 02:58 Your format may look different, but it should bring up your date.
• 03:02 Now this obviously is not a month end, so wouldn't it be cool if we
• 03:07 just go to the transform tab and choose date, month, end of month.
• 03:13 And boom, just like that we have a month end.
• 03:15 Isn't that excellent?
• 03:16 Now, this works really, really well, because what's gonna happen is
• 03:21 when I expand this column to get this table out, the value in this row here.
• 03:26 For January 31, will be assigned to every single row for this table.
• 03:31 February 29, every row for this table will inherit that date.
• 03:35 So as you can see, when I go and
• 03:36 say, let's expand this column, don't use the original column name as prefix,
• 03:41 because we don't want contet.account content.department content.amount.
• 03:46 We'll say OK, and boom, just like that everything opens up.
• 03:51 The only things I really need to do now is set my data types.
• 03:55 So i will grab my account in department.
• 03:57 Right-click, change type to text.
• 04:01 We'll set my amount to currency.
• 04:04 We'll force my month ends again one more time just to date, so
• 04:06 we've got everything on the same step.
• 04:09 And we'll go and we'll call this guy here, transactions.
• 04:14 And finally, we're going to go home > close, and load.
• 04:19 So this will create our data for us.
• 04:21 But what you're going to see, is that something weird happens.
• 04:24 We get 6,085 rows, and we get one error.
• 04:28 Now what I'm gonna do at this point is I'm gonna hit refresh.
• 04:31 And what you're gonna see is it reloads it.
• 04:33 This time I've got 6,085 errors.
• 04:36 If I do it again, we're gonna be up at 12,169 errors.
• 04:41 You go, what is going on?
• 04:43 Well, the secret to this is figuring it out is here.
• 04:46 Let's go right-click and edit our query.
• 04:49 And this is something I want to bring to your attention.
• 04:50 Because this could happen if you're combining things within the same workbook.
• 04:54 Notice I have a bunch of errors.
• 04:56 You may not, that's fine.
• 04:58 We're gonna go back and click on the source step.
• 05:01 And what you'll see when I do that, it says, okay, what's the problem.
• 05:05 We have January, February, March, we got the print area, and
• 05:07 we got range department.
• 05:09 PowerCore uses previews that get cached, so
• 05:13 sometimes we need to click this refresh preview button.
• 05:16 And look at this, when I do, transaction.
• 05:18 Where did that come from?
• 05:20 It's the name of the query that I created.
• 05:22 So what's happening is that the output is getting recycled back into the input here.
• 05:28 If I filtered out the rows, we've filtered out the print area and
• 05:32 ranged apartment, but that didn't get rid of transactions.
• 05:36 I then rename the column, replace values, convert it to a date, and got an error.
• 05:41 So if we see this happen, we can fix this very quickly now that we know its there
• 05:47 by highlighting the month_and column and saying remove rows with errors.
• 05:53 Insert the step and now that will take care of it and
• 05:56 get rid of the transactions table.
• 05:59 From here I can calculate the rest of the way,
• 06:02 everything will be good and now when I hit close and load to,
• 06:05 you'll see that my query will reload with 6,084 rows as it's supposed to.
• 06:10 So this is kind of the key.
• 06:12 If you're reaching in and trying to pull up multiple tables together using
• 06:15 excel.currentWorkbook or multiple-named ranges,
• 06:18 just be aware that the query that you've created will become part of the output.
• 06:22 So you must filter that out.

Lesson notes are only available for subscribers.