Getting Data from Excel Ranges

Quick reference

• 00:05 >> Now, in this video we're gonna do something pretty darn cool.
• 00:08 We've already got a Departments table and we already have a Transactions table.
• 00:14 And what we want to do now is we want to pull in Budgets, but
• 00:18 Budgets has a problem.
• 00:20 Budgets is already pivoted and
• 00:22 this is a real challenge when you wanna serve up a pivot table.
• 00:26 Because what I need is not this department account and
• 00:29 then each individual year going across here, that's a nightmare.
• 00:33 What I actually need is something that says Department, Account, Date, Amount and
• 00:38 I need that to turn into one really long table.
• 00:41 Now, you can see if I go and say end down arrow that this thing goes for
• 00:46 about 277 rows, there's a lot of stuff.
• 00:49 I've even got values that need to be filled down for the individual apartments.
• 00:53 This is a pretty gross looking table.
• 00:56 And if you had to unwind this manually this would take an awful long time.
• 01:01 So what we're gonna do is we're gonna use Power Query and show you why this is
• 01:05 a super useful tool that we use to actually work with Power Pivot.
• 01:08 The first thing that I need to do is I need to pick up the range of data that I
• 01:12 want to work with.
• 01:13 So I'm gonna grab this guy here, I'm starting in the top left, and
• 01:17 I'm gonna hold down my shift key and press control end.
• 01:21 And that will grab the entire rectangular range of data from the top left corner,
• 01:26 holding down shift will grab the contiguous range.
• 01:29 And then pressing control end as I'm holding down shift will go to
• 01:32 the last cell in the workbook that's used and that will actually grab it.
• 01:35 Now, I could, obviously, use the mouse to select this or what not as well.
• 01:39 This gives me the range of data that I want to work with.
• 01:43 Now, the challenge is if I try and create a new query from here at that
• 01:47 point what's gonna happen is it's gonna try and put a table style on it.
• 01:52 But that's problematic because as soon as you put a table style on
• 01:56 a table it locks down the headers.
• 01:59 And what you didn't see is that all of these headers are driven based on
• 02:04 formulas that point back to the original information here.
• 02:08 So we can't have a table style.
• 02:11 So let me grab this again, hold down shift, press control end, and
• 02:15 grab the entire set.
• 02:17 So how am I gonna do this if I can't put a table style on it?
• 02:20 Well, the answer is I'm gonna go to Formulas > Name Manager and
• 02:24 I'm gonna define a name over top of this.
• 02:27 I'm gonna say New and I'm gonna call this guy something like RNG Budget.
• 02:33 So this is gonna be my budget, RNG is just my way of shortcut naming for
• 02:38 range, and I'm gonna say OK.
• 02:40 And there we go, we have Range Budget, it looks a little
• 02:43 bit different than Departments, which is the table we created earlier.
• 02:47 But the beauty of this now is that I can go click over here and from my drop down,
• 02:51 I can select Range Budget and it will grab that entire area.
• 02:55 And now when I go to create my new query, again, Power Query tab for
• 02:59 Excel 2013 users, Data Tab for 2016.
• 03:01 And I go and Create a New Query > Get Data > From Other Sources > From Table or
• 03:07 Range it will pull this in from a range without applying a table style.
• 03:13 Which is nice cuz it doesn't lock down the headers.
• 03:16 Now, there's a little bit of work that I have to do here.
• 03:18 What's happened is it's pulled the data in, there's no headers because it's not
• 03:22 a table, it's then changed the type of the columns and that's fine.
• 03:25 What I need to do is I now need to do a couple things.
• 03:27 I need promote this first row of headers so I'm gonna do that here.
• 03:32 And as soon as I do that it promotes the first row and
• 03:34 then locks down the data types by changing the column types.
• 03:38 This is problematic because you can see that we've actually locked
• 03:41 down the headers in the code at the top here and there's lots of them.
• 03:44 So what that means is if the years change this is gonna cause an error because these
• 03:48 columns won't exist anymore.
• 03:49 So we're gonna delete this step by clicking the X.
• 03:54 There we go, now it's promoted headers, but
• 03:57 we don't have a change type step, there's nothing hard coded.
• 04:00 I'm now gonna take my first column here and you'll notice it's 110, it's got null,
• 04:05 null, null.
• 04:06 I'm gonna right-click and I'm gonna choose to Fill Down.
• 04:09 And this will fill the 110 all the way down until it encounters
• 04:13 the first row that's not null, which was row 51 that had 120 in it,
• 04:17 and then it'll fill those values down.
• 04:20 In addition,
• 04:21 I remember the very last row of this data set was something called a Grand Total.
• 04:25 And I don't need this in my data because, naturally,
• 04:28 the pivot table can recreate this for me.
• 04:30 So I'll uncheck the Grand Total and say OK so we get rid of those.
• 04:35 I'm gonna take a look across my table now.
• 04:38 And I find, look a Total 2009 column, let me go and delete that.
• 04:45 And also let me come across a little further, and
• 04:50 find this guy, and I'm also going to delete him.
• 04:55 I now have a bunch of data that has no totals or subtotals, but
• 04:59 it's still pivoted.
• 05:00 But check this out, grab Department, hold down control,
• 05:04 grab Accounts, right-click, and Unpivot Other Columns.
• 05:08 And, boom, at this point the entire table is unpivoted for me very nicely and
• 05:13 that's pretty cool.
• 05:14 I can now grab my Attribute column, double click the header, and change it to date.
• 05:20 Now, there is one challenge here, you think, all right,
• 05:22 let's convert it to a real date cuz it's text right now.
• 05:24 And when you do that you get a bunch of errors.
• 05:27 And the reason is because it can't figure out what to do with these things on
• 05:31 the end.
• 05:32 But if I go and change it to a Date/Time, that'll change it into dates and times.
• 05:39 And then I can come back and say let's change this to a date.
• 05:44 And it says, would you like to replace the current step?
• 05:46 Well, that would give me the errors again.
• 05:48 Or would you like to add a new step?
• 05:49 And because everything here happens in sequential order because this has become
• 05:54 a Date/Time, we can say Add New Step.
• 05:56 And it's now gonna change a date time into a date and drops the times for me,
• 06:00 which is kind of nice.
• 06:02 I can now rename my last column to Amount and
• 06:06 set this to be a Currency.
• 06:09 And, finally, I'll rename my table to Budgets and I'm good to go.
• 06:16 My data is prepped, it's ready, my departments and
• 06:19 accounts are all set in text like the rest of the model.
• 06:22 I'm now gonna go and say Close and Load To.
• 06:25 And we're gonna load this to Only Create Connection,
• 06:29 add it to the Data Model, and say OK.
• 06:32 This will proceed to take those 277 rows, unpivot everything, and
• 06:36 leaves me with 6,500 rows of data.
• 06:38 But I notice that it's in the Other Query's folder, whoops.
• 06:42 I can't drag and drop from this interface, like I can inside the window.
• 06:47 But I can right-click > Move To Group, and
• 06:50 chose to move it to the Data Model folder so that it shows up in here.
• 06:54 And if the order's important to me I can always right-click and
• 06:58 say Move Up to get it into alphabetical, or whatever order I want to see it in.
• 07:02 So there we go, we've now got our Departments table,
• 07:05 our Budgets table, and our Transactions table.
• 07:08 Even if the data doesn't come in in great format,
• 07:10 we can reshape it with Power Query, and land it into Power Pivot.
• 07:13 So now we can actually start to build our model.

Lesson notes are only available for subscribers.