Managing Power Queries

Quick reference

• 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.