Acquiring and Updating Power Query

Quick reference

• 00:04 Before we get into working with Power Query,
• 00:06 let's talk about how you even get your hands on.
• 00:10 We're gonna start with Excel 2010/2013.
• 00:13 Power Query for Excel 2010 and 2013,
• 00:16 is a free add-in that you can download from Microsoft.
• 00:19 To get it, you can simply go to this URL and click the Download link.
• 00:24 Or if you prefer, you can do a search for download Power Query and
• 00:28 it'll find that URL for you, so that you can click on it and download the software.
• 00:33 In Excel 2016, things just get a little bit easier.
• 00:36 Power Query is actually now built into the product so you don't need to do anything.
• 00:41 The only thing that I will throw out there though is that the connectors that you
• 00:44 have to connect to your data may actually vary.
• 00:47 And when you're working with a Professional Plus Q, or a Pro Plus Q,
• 00:52 you will have all of the connectors.
• 00:53 But if you're working with Office Home,
• 00:55 you may not have access to things that are considered business or enterprise sources,
• 00:59 things like SQL Server or Microsoft Exchange.
• 01:02 So while you may not be able to create new data in every version of Power Query,
• 01:09 for Excel 2016, you will still be allowed to
• 01:12 refresh any connectors that are built in another workbook.
• 01:15 So this is actually a pretty good thing.
• 01:17 Because it means that if we have somebody who's building this enterprise solution,
• 01:21 and they send it to somebody who has a lower version of Excel,
• 01:24 you can still refresh it.
• 01:25 And it'll still work, so that's good.
• 01:27 Power Query is also available inside a program called Power BI Desktop.
• 01:33 And in this one, Power Query is actually built right in as well.
• 01:36 Every time you download Power BI Desktop, this is just the Get Data Later that's
• 01:40 actually built into that particular software.
• 01:42 So there's no extra steps that need to be done here.
• 01:45 So once you've got Power Query, where do you find it?
• 01:48 And the answer to this depends because it's changed a couple of times.
• 01:52 In Excel 2016 and higher, you need to look for the Get & Transform Group.
• 01:58 And on there, you'll find that there is gonna either be a button
• 02:01 that is called New Query which will be about a third of the way across.
• 02:05 Right about where you can see the Refresh All button in this screenshot.
• 02:09 That's gonna be the classic Excel 2016 standalone,
• 02:12 nonsubscription installation is how that one will actually look.
• 02:16 In the subscription version of Excel,
• 02:19 it'll be under the Get Data button as you show right here.
• 02:21 And this what I'm gonna use when I'm actually shooting the course,
• 02:24 is the Excel 2016 subscription version.
• 02:28 In Excel 2010/2013, it's gonna look a little different.
• 02:31 You're gonna have an entire tab for
• 02:33 Power Query, and all of your commands will be on there.
• 02:36 So if I'm calling out to go from file, From folder or something like that,
• 02:40 you would go to the Power Query tab and find the From file button.
• 02:44 For Excel 2016 and higher, users will come to the Data tab and find the Get Data or
• 02:48 New Queries button to find it from file, from folder.
• 02:51 So some slightly different entry points.
• 02:53 But as a consistent piece here, I'm just gonna say, create a new query and
• 02:57 then I will give the rest of the directions.
• 03:00 So that may mean go to the Power Query tab or it could mean go to the Data tab and
• 03:04 find the new queries or Get Data button.
• 03:07 In Power BI, we just go the Home tab and
• 03:09 there´s a great big button called Get Data.
• 03:11 That's never changed and that's where all of the query layers are actually found.
• 03:16 Now one of the amazing things about Power Query is that Power Query gets
• 03:20 updated every single month, and this is amazing.
• 03:24 Because they're constantly adding new features and making things better and
• 03:28 making things easier.
• 03:30 And I need to call this out specifically for two reasons.
• 03:33 One, to make sure you can get the updated version.
• 03:35 But number two, if you see something in the course that doesn't look exactly
• 03:39 the way that it shows on your screen today, it's very likely there's been a new
• 03:43 update that's actually has been added to make things easier.
• 03:47 So let's start with Excel 2016 and higher subscription versions.
• 03:52 And you'll be pleased to know that if you're on subscription,
• 03:55 the updates are applied automatically with all the new features.
• 03:58 Now it does depend what channel you're on.
• 04:01 You can be on the Insider Channel, which gets its builds and
• 04:03 new features very quickly.
• 04:05 You could be on the Targeted Channel which gets them a couple of months later.
• 04:09 Or you could an be on a deferred channel which gets them even later.
• 04:12 But regardless, these feature updates will come for you and
• 04:15 they'll be applied automatically.
• 04:17 And if you're ever curious, you go to the file account.
• 04:20 You check your Update Options box.
• 04:21 You see here to see if you can force a newer update onto your software.
• 04:25 So that channel will depend on what IT has configured for you.
• 04:29 You may be able to influence them to give you a more advanced one, or
• 04:32 not, depending on how your IT department deals with things.
• 04:35 That's the subscription method for Excel subscription versions.
• 04:39 There's also a nonsubscription version of Excel 2016 and higher.
• 04:44 These versions include bug fixes only, no new features.
• 04:48 And that's really frustrating.
• 04:49 So if you're actually on one of the old versions of nonsubscription software,
• 04:54 you're gonna find that it's not updated at all.
• 04:57 There's gonna be things inside these videos that you're gonna see which, well,
• 05:00 unfortunately, you're not gonna have access to them.
• 05:03 There's not too many that are significant differences but there are a couple.
• 05:07 The other thing that's really unfortunate about this,
• 05:10 the Excel 2016 that's standalone was actually released in October 2015.
• 05:14 And that's the version Power Query uses.
• 05:16 And it has a couple of pretty concerning areas of shortfall, I think,
• 05:21 which will get called out as we go through these things.
• 05:24 Excel 2010/2013, it's a manual download of the Power Query
• 05:29 update files that you can actually do once a month.
• 05:32 Or if you're working with an IT department, as it matters,
• 05:35 I would recommend that you probably harass them about once every quarter or
• 05:39 once every six months, depending on how much credibility you've got with IT.
• 05:42 The reason being is you don't wanna drive them crazy but
• 05:45 new updates deliver new features and make things easier.
• 05:49 If you're on Power BI Desktop, it's included with a monthly update.
• 05:53 It's still a manual download, but
• 05:55 you're actually updating the entire Power BI Desktop software.
• 05:58 And Power Query is actually rolled right into that from the beginning.

Lesson notes are only available for subscribers.