Locked lesson.
About this lesson
It's all about the refresh - something that will be problematic if you can't change the source data's file paths. Here we will explore how to do this, as well as show you how to keep your Power Queries organized.
Exercise files
Download this lesson’s related exercise files.
Managing Power Queries.xlsx501.4 KB Managing Power Queries - Completed.xlsx
502.3 KB
Quick reference
Managing Power Queries
An overview of updating a query.
When to use
When you need to update an existing query in the Power Query Editor.
Instructions
The general process is:
- Display the Queries Pane in Excel
- Excel 2010/2013: Go to the Power Query tab --> Show Pane
- Excel 2016: Go to the Data tab --> Show Queries
- Excel Subscription: Go to the Data tab --> Queries & Connections
When the Query Editor opens
- Select the step to edit from the Applied Steps list on the right
- Click on the gear icon next to the selected step (if available)
- Update the query as needed and select OK
- Click the Close & Load button
Hints & tips
- When you create a Power Query, the file path is always hard-coded to the computer used to build the query
- You can create groups for organizing your queries in either Excel or in the Query Editor
- Drag and drop functionality is only available in the Query Editor
- 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.