Managing Power Queries

Quick reference

• 00:05 It's now important to show you how to update your Power Queries,
• 00:08 so that they actually refresh when you need to.
• 00:11 And you'll notice that every single example file that we have throughout
• 00:14 the rest of this course always starts with an info page that looks like this.
• 00:18 And the reason being is because the query that actually pulls the information from
• 00:22 the database is hard coded to a path that exists on my PC.
• 00:27 And reason being is because when you create a Power Query,
• 00:30 it always hard codes back to the path on the PC.
• 00:33 So, I need to show you how to update this, to where you've stored the database, so
• 00:37 that you can actually refresh the data.
• 00:39 Now, where you'd go to get into this again depends on
• 00:42 which version of Excel you're on.
• 00:44 On Excel 2013 you're gonna go to the Power Query tab, and in Excel 2016,
• 00:49 you're gonna go to the Data tab.
• 00:52 Now, the button that we need is always around in this area.
• 00:56 In newer versions of Excel 2016, it's called Queries and Connections.
• 01:01 In older versions of Excel 2016,
• 01:03 the non-subscription versions, it's gonna be called Show Queries.
• 01:08 And on the power query tab in Excel 2013, it's called Show Pane.
• 01:13 So, three different names depending on which version of Excel you're
• 01:15 actually using.
• 01:17 Regardless, when you click on it, it'll pop open the Queries pane on the right
• 01:21 hand side that lists the transactions query.
• 01:24 Now what we're gonna do to update this is, we're going to right-click, and
• 01:29 we're gonna say Edit.
• 01:32 And when you go in and take a look, that'll actually bring us in here.
• 01:34 Now, you may or may not see this, there may be a yellow message that comes across
• 01:39 the top, and it's important to understand how Power Query works so
• 01:42 that we know what we need to do.
• 01:44 Don't worry about it, if you see you a yellow message, all good right now.
• 01:47 Here's the thing with Power Query.
• 01:49 Remember in the last video, we went through and we did certain things,
• 01:53 well those all get recorded in the Applied Steps window on the right hand side.
• 01:57 The first thing that happened was we connected to the database and
• 02:01 that is the source step.
• 02:03 So, if you click on this, this may give you a yellow message to say, I can't find
• 02:08 this, and that's okay, because there's a gear icon on the right hand side.
• 02:13 If you click on the gear icon, it'll pop open the box that allows you to go and
• 02:17 actually change or update the file pass.
• 02:19 So, you can see you can browse,
• 02:21 to actually pick up where the go skills data for the mulligans database is found.
• 02:26 So you wanna browse to where that's located on your system,
• 02:29 point to that particular file, and at that point, everything should be good to go.
• 02:33 You can say okay, and now, it'll refresh to show you the individual table.
• 02:38 Now you remember, that we connected to that data based, and then it brought up
• 02:41 a preview window, and we chose to go and actually deal with this specific table.
• 02:46 That step was recorded as navigation.
• 02:49 And it drilled into the original table that we're looking at.
• 02:52 Notice the ID column is still there, the dates are still formatted as both dates
• 02:56 and times, so all of that kinda stuff happened for us originally.
• 03:01 What we then did is, went and removed the ID column,
• 03:05 notice it recorded a step for us that says removed columns.
• 03:09 We then changed the type of this particular column here, to make it a date.
• 03:15 And then, we replace the dash b here with dash space b, and
• 03:20 that was recorded as a replaced value step.
• 03:22 So, the nice thing about this is that, it actually records
• 03:26 all of the individual steps that we've done, so that when we want to go and
• 03:30 actually refresh the database, I'm now gonna hit Close and Load.
• 03:36 With a simple Data > Refresh All, you'll notice that the Power Query is
• 03:40 gonna go and start spinning, and it's going to pull in the up to date data,
• 03:45 run it through the process and then load it to the data model.
• 03:47 And it does that fairly quickly, which is pretty nice.
• 03:50 Now there is something else that I'd like to do here as well, and
• 03:54 that's I'd like to group and organize my queries in this query pane, and
• 03:58 we have two places we can do that.
• 03:59 We can do it from here, by choosing right-click, Move to Group > New Group.
• 04:05 Or we can also, if we edit the query, we can come in and expose
• 04:10 that exact same pane by clicking on the queries button on the left hand side here.
• 04:15 The difference between what you saw in Excel and what you see here,
• 04:18 is that this queries pane allows drag and drop, where the one in Excel does not.
• 04:23 I'm gonna-right click and say, Move to Group and create a new group here.
• 04:29 This group is gonna be called Data Model, and
• 04:32 every query that I go to put in this group is gonna load into Power Pivot.
• 04:38 While I'm here, I'm also gonna create a new group.
• 04:40 I'm just gonna click in the white space here and say New Group, and
• 04:44 I'm gonna create a new group here for Staging Queries, and
• 04:49 these are something that I'm gonna use a little bit later.
• 04:52 I'm gonna move this up by saying right-click, and Move Up, so
• 04:55 that it actually comes above data model, that's the way I like to organize mine.
• 04:59 So this will collect some staging queries that I'll eventually end up using to
• 05:02 reshape some of the data in ways that I need.
• 05:05 Right now, I can click here on Transactions,
• 05:07 I can see all of the steps that have actually happened.
• 05:09 And when I hit Close and
• 05:11 Load, there's no real change to what's going on in the data model.
• 05:14 But I've got things nicely organized and ready to go, and now I'm ready to go and
• 05:18 get some more data.

Lesson notes are only available for subscribers.