Locked lesson.
About this lesson
Updating a Power Query solution to a new file path, allowing us to refresh the file with a click.
Exercise files
Download the Excel workbook used in the video tutorial and try the lesson yourself.
Managing Power Queries.xlsx2.7 MB
Quick reference
Managing Power Queries
Modifying an existing Power Query.
When to use
When you need to edit a Power Query that feeds a PivotTable.
Instructions
Open the Queries Pane
- Go to the Data tab > Queries & Connections (or Show Queries in Excel 2016)
- Select the query by either:
- Right clicking the query > Edit
- Double clicking the query
Edit the Query
- Click the gear icon next to the step you would like to edit
- Make the changes and click OK
Key points to remember
You can update the source file path for all instances at once, instead of doing each query individually
- In the Query Editor, go to the Home tab > Data Source Settings
- Select source file
- Click Change Source
- Browse for the file and click OK
Power Query uses cached previews to speed things up
- Sometimes you may need to refresh your PivotTable twice
- The first refresh is for the PivotCache, the second is for the query
Hints & tips
- Power Query has a VAST amount of data sourcing and transformation options… so many that GoSkills has an entire course on just this technology!
- See: https://www.goskills.com/Course/Excel-Power-Query/Lessons
- 00:04 In this video, we're going to look at how to manage Power Queries.
- 00:08 And this is super, super important because when you create a file that
- 00:13 actually uses a Power Query to extract data from somewhere else,
- 00:16 it generally hard codes the file path in most scenarios.
- 00:20 So, this can be problematic if the file isn't there, maybe you've sent your
- 00:24 file to somebody else, it's pointed to a path that's on your hard drive, and
- 00:28 that data source file is not there, you're going to get an error.
- 00:31 Now, this particular file here, we have a pivot table that is built
- 00:34 off of an external data source by using Power Query.
- 00:36 You'll notice when I clicked inside, the pivot table all looks fine,
- 00:39 and indeed I could slice and dice any way I want, it's all going to work.
- 00:43 But the challenge is,
- 00:44 is that this thing is actually pointed to a data file that does not exist anymore.
- 00:48 So, what I need to do is figure out how to manage that.
- 00:51 To manage queries, we're going to go to the Data tab.
- 00:53 And the reason I'm showing it to you in this view right now is because this is
- 00:56 what you will see when you open Excel for the first time.
- 00:59 The queries pane won't be there.
- 01:02 So to find this, we want this pain called Queries & Connections on the Data tab.
- 01:06 Now in Excel 2016, this was called show queries.
- 01:09 It was right around the same place, but 2019 and higher,
- 01:10 it's called Queries & Connections.
- 01:12 And when you click it, it'll actually bring back the Power Query pane for you,
- 01:16 which is great.
- 01:17 Now, here's what's going to happen.
- 01:19 I'm going to try and run a refresh on this.
- 01:20 And what you'll see is it'll get this nice little error message is going to pop up
- 01:23 that tells me that the data source can't be found.
- 01:26 So, how do I fix it?
- 01:28 That's the big question.
- 01:30 In order to actually modify a query, you need to edit it.
- 01:33 And there's two ways to do that, but
- 01:35 both of them require the queries pane to be open.
- 01:38 You can right-click and choose to edit your query.
- 01:42 Or you can just double-click with your left mouse button
- 01:45 in order to open up the query stack itself.
- 01:48 Now what you see here is that we have a data format error, it says,
- 01:50 it can't find this file.
- 01:51 This is what we call a step level error and
- 01:54 it will prevent your data from loading.
- 01:56 Now in this case, it actually goes all the way back to the source step,
- 02:00 because it can't find this file.
- 02:02 Although it looks like it can.
- 02:04 But if you hit the refresh preview button, you'll see that this is now going to fail.
- 02:08 And this is a key thing here on Power Query, it uses cached previews to try and
- 02:11 speed things up.
- 02:13 So, not a big deal.
- 02:14 Now that we know this, how do we actually edit it?
- 02:16 And there's two different ways to do this.
- 02:19 One of them is you can click the gear icon, right here beside the source step.
- 02:23 And that will actually bring you up the dialogue that allows you to go and
- 02:26 browse for your file path.
- 02:28 So I'm going to browse for that and say, hey,
- 02:30 this data is not in the folder where you think it is.
- 02:33 It's going to be in this one here called using external data in my examples.
- 02:37 And there is the database that I want.
- 02:38 I'm going to say Import and I'm going to say OK.
- 02:42 Now, this is happening because I actually pointed to something in
- 02:46 the same folder and then removed the data source to replicate this problem.
- 02:50 You'll now notice that when I do this,
- 02:53 if I refresh the preview, everything looks good.
- 02:56 And I can actually even go and step all the way down to the very last step
- 02:59 once this preview is actually refreshed.
- 03:02 And I'll show you that all the data is now there, which is excellent.
- 03:05 So, it's still doing its refresh here.
- 03:07 There we go, we're finished.
- 03:08 If I go all the way down to the very last step,
- 03:10 you'll see that all the data is now there.
- 03:12 Now, there's another method to do this as well.
- 03:14 You can actually click on the data source settings dialog, and
- 03:17 when you click on this, you'll notice that here is the piece.
- 03:19 So I can now go and say Change Source, and
- 03:21 I could change it right through this dialog as well.
- 03:24 It gets me to the same place, although this one will actually change
- 03:27 every instance of this connection in the entire file.
- 03:30 Rather than just the instance for
- 03:32 this one query because you're allowed to create multiple queries.
- 03:36 Now that we've done that, we can say, hey, this is pretty cool.
- 03:39 We can just hit the Close % Load button,
- 03:41 it will re-inherit the default that we set up for it last time and
- 03:44 you'll see that the transactions is now going to go and load.
- 03:47 Here we are, we're loading the data.
- 03:48 It will stream in the 348,840 records, and once those are in place,
- 03:53 we can now go and right click and refresh our pivot table and
- 03:57 that will actually drive the pivot table to refresh.
- 04:00 And this is kind of a key thing is that sometimes, based on the way the query
- 04:04 chain is working, you actually need to refresh twice.
- 04:08 The reason being is that it will refresh the pivot cache first,
- 04:11 and then refreshes the query second.
- 04:12 So the order is not exactly right.
- 04:15 So sometimes, a double refresh is required in order to make this work.
- 04:19 And that is essentially what you need to know what managing Power Queries.
- 04:22 Again, there's a full course on Power Queries in the GoSkills catalog
- 04:26 that you should really check out because it's an amazing tool and will help you
- 04:29 with pPivot tables for certain, but also many other data sets as well.
Lesson notes are only available for subscribers.