Acquiring Power Pivot and Power Query

Quick reference

• 00:05 In this course, we're gonna make use of two components to build modern business
• 00:09 intelligence solutions using best practices.
• 00:11 The first one is Power Pivot, the second one is Power Query,
• 00:14 let's make sure first that you've got Power Pivot.
• 00:18 So, the nice thing with Power Pivot is it's actually built into Excel 2013 and
• 00:23 higher at least, it sort of is.
• 00:25 The reality is you have to be using the correct skew or
• 00:29 stock keeping unit of Microsoft Office.
• 00:32 That means that in order to get the Power Pivot tab,
• 00:35 you must be using Office Professional Plus or an Office ProPlus subscription,
• 00:40 or alternately, a stand-alone version of Excel.
• 00:43 What this means is that no matter how you bought your software,
• 00:48 if it doesn't contain Professional Plus or ProPlus.
• 00:51 Or if you haven't bought it and installed it over top as just the Excel product,
• 00:56 you may not have access to Power Pivot.
• 00:58 Things like premium SKU do not have Power Pivot in them and this is something that
• 01:03 can be a little bit frustrating, so no other SKU has this interface.
• 01:07 Now the key thing is that it's just about the interface to build new models.
• 01:11 You can have somebody on Excel standard, they can refresh a Power Pivot model,
• 01:15 they just can't get into the editing interface in order to actually do things.
• 01:20 So you wanna make sure you have Professional Plus or ProPlus on your
• 01:24 Office side and you can find that in the file account in order to get that.
• 01:28 Or alternately install Excel over top of your existing Office installation, and
• 01:32 that will actually give you Power Pivot.
• 01:35 So how do you know that you have it, where do you find it?
• 01:38 Well, once you've got it activated,
• 01:40 it should actually give you a power pivot tab.
• 01:42 If it doesn't, in Excel 2016, you gonna go to File> Options> Advanced and data.
• 01:49 Now you may or may not, depending on how current
• 01:52 your SKU is need to go to Advanced, it may just be File > Options > Data.
• 01:57 And you're gonna check the box next to Enable Data Analysis add-ins, Power Pivot,
• 02:01 Power View and 3D Maps, okay?
• 02:03 There's a one check box for that, so you just check that.
• 02:06 In Excel 2013, you would go to File> Options> Add ins.
• 02:11 Down in the bottom, there's gonna be a little drop down box called
• 02:14 Manage COM Add-ins, and then you choose Go on the bottom right hand corner.
• 02:19 Inside that, it'll pop up a little window of all of the COM Add-ins that you have
• 02:23 available and installed on your system, and
• 02:25 you'll check the box next to Microsoft Office Power Pivot for Excel 2013.
• 02:29 It's a little bit of a hunt to try and find those,
• 02:31 once you've done that through either route, Power Pivot should show up.
• 02:35 If it doesn't, you're gonna need to talk to your IT department for support.
• 02:41 Now what about this other tool, Power Query?
• 02:43 Well, Power Query is a tool that we use to reshape data to make modeling easier.
• 02:48 It's a really important component, and the best practice to actually run through
• 02:52 Power Query, in order to build your Power Pivot models.
• 02:54 And that's why this course actually only supports Excel 2013 and
• 02:58 higher, because it's not quite as rich to work with in Excel 2010.
• 03:02 Having said that, you can get this tool in Excel 2010 and 2013.
• 03:06 It's a free download from Microsoft and you'll need it if you're on Excel 2013 in
• 03:11 order to actually complete working through this particular course.
• 03:14 You can get that here or by doing a Google search for download Power Query for Excel.
• 03:22 That term will get you right to this page and you can download and install it.
• 03:25 In Excel 2016, you know what, you're fortunate enough Power Query is built
• 03:29 right in so you didn't have to worry about that.
• 03:31 Now the connectors you see on screen may vary from what I have,
• 03:34 that's because of the SKU that you would have purchased.
• 03:37 But at the end of the day,
• 03:38 Power Query again will allow you to refresh anything that is built.
• 03:41 Everything you'll see in this course,
• 03:43 you're able to do with any SKU of Power Query, so that won't be a problem.
• 03:46 So if you're in Excel 2016 It's built right in, if you're in Excel 2010 or 2013,
• 03:51 you'd have to go and actually download Power Query manually and
• 03:54 install it with your Office.
• 03:55 So once I have Power Query installed, where do I find it?
• 03:58 Well, it's actually moved around as Microsoft is rolling it into the product.
• 04:03 In Excel 2016,
• 04:04 you're gonna find this on the Data tab on the Get and Transform Group.
• 04:09 The view that you're looking at on screen right here, is the very latest view where
• 04:13 Microsoft has actually gone and harmonized the ribbon so
• 04:16 you can see that there's a button on the very left-hand side that says, Get Data.
• 04:21 If you're in Excel 2016 and you don't see that, you wanna look about six or
• 04:25 seven buttons to the right, and you'll see one that's called New Query.
• 04:29 So, older versions of Excel 2016 that haven't been updated,
• 04:33 you'll find your new queries under New Query on the Data tab.
• 04:37 Newer versions of Excel 2016 will have it on the Get Data tab.
• 04:41 Whatever I call out to go to a new query that's where it's gonna be is on
• 04:45 the Data tab under one of those two buttons.
• 04:47 In Excel 2013, it's a little bit different again,
• 04:50 you actually have an entire Power Query tab so all of the commands will be there.
• 04:54 And on the Power Query tab you would see from file from database from online
• 04:58 services it won't be buried under a menu so that can be a little bit confusing.
• 05:02 Now the last thing about Power Query that’s worth knowing is that it actually
• 05:06 gets updated every single month.
• 05:07 There’s new features that come out, they add more robustness to it,
• 05:10 they fix a few bugs that happen in there every now and then, so
• 05:14 you wanna keep your Power Query up to date as much as you can.
• 05:18 In a Subscription Versions of Excel 2016, this happens for you automatically so
• 05:22 as long as you see that subscription product on the right you're good to go
• 05:26 these things are just gonna come in on you on a regular basis.
• 05:29 Non-subscription versions, well unfortunately it's not quite so
• 05:32 good there you only get bug fixes no new features.
• 05:35 In fact, the new feature set, if you've installed under a volume licensing build,
• 05:40 is not updated since October, 2015, which is a real, real shame.
• 05:44 You want to be on subscription if at all possible, the product is much more stable,
• 05:48 and I'll talk about that near the end of the course as well.
• 05:51 In Excel 2013, because it's not a subscription product in quite the same way
• 05:55 here and Power Query's a separate add-in, you actually have to go down and
• 05:59 manually download it and install it.
• 06:01 Now you don't need to do it every month,
• 06:02 I recommend if you've got an IT department that actually helps you with these things.
• 06:06 You only bug them about once every three months to try to get some new features
• 06:08 there, cuz you don't wanna wear out your welcome.
• 06:10 But at the end of the day, it is a manual process there, Excel 2016,
• 06:14 automatic if you're on subscription, Excel 2103, it's a manual download.

Lesson notes are only available for subscribers.