Locked lesson.
About this lesson
You won't always want to format your source data as an Excel Table, and in this module we will show you how to get it into Power Pivot anyway.
Exercise files
Download this lesson’s related exercise files.
Getting Data from Excel Ranges.xlsx561.6 KB Getting Data from Excel Ranges - Completed.xlsx
672.4 KB
Quick reference
Getting Data from Excel Ranges
An overview of importing data from defined Excel ranges.
When to use
When you want to bring data from a worksheet into Power Query without applying table formatting.
Instructions
To define a named range you can either:
- Highlight the range in the worksheet --> Formulas --> Name Manager --> New --> type name
- Select the range in the worksheet --> go to the Name box --> type name
To load data to Power Query from a named range
- Select the name from the Name box to ensure it is selected
- Create a new query --> From Other Sources --> From Table/Range
- Prepare the data as required
Unpivoting Data
- Select the column(s) that you want to keep as is
- Right click the column header and select Unpivot Other Columns
- Rename the Attribute and Values columns
- Set data types as needed
- Name the query
- Click the Close & Load To… button
Hints & tips
- Quickly select the entire range of data in a worksheet by clicking on the first cell, then holding down the SHIFT key and pressing CTRL + END
- The Fill function fills data up or down, but only into cells containing the null keyword
- 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.