Viewing Query Dependencies

Quick reference

• 00:04 As you build more and more queries,
• 00:07 there's gonna come a time when you need to actually go back and audit a file.
• 00:11 Maybe it's one that you've built, or maybe it's one that somebody
• 00:14 else has given to you, to try and figure out what query needs to be changed or
• 00:19 what queries are actually being used and which ones are not.
• 00:22 So how do you go about doing that?
• 00:24 Well the first stage is to open up the queries pane on the right hand side so
• 00:29 that you can see the listing of the queries.
• 00:32 You'll notice that we can scan them quickly and
• 00:34 notice that these guys are in connection only, connection only,
• 00:36 connection only, connection only and Full Anti actually has four rows loaded.
• 00:40 This is an indicator that one of the queries is being used somewhere.
• 00:44 It could be loading to the worksheet,
• 00:45 Something that we can see if we click on it, cuz it'll activate the table for us.
• 00:50 If it doesn't, that means it's being loaded into Power Pivot's data model so
• 00:55 it's still going to be in use.
• 00:57 This tells you that the query lands data somewhere.
• 00:59 It doesn't tell you that each column is being used, that's something that you
• 01:03 would have to trace through to see whats happening with your formulas, or
• 01:06 your pivot tables, or your Power Pivot data model.
• 01:08 But at least you know the data is being loaded somewhere so
• 01:11 the query itself is in use.
• 01:14 But what about all the connection only queries?
• 01:16 How can we tell if they're actually being used or if somebody just
• 01:20 temporarily set them to say I want this query, I'm gonna keep it around but
• 01:23 I'm not actually gonna load it anywhere and it's not being referenced by anything.
• 01:26 How do we know?
• 01:28 Well, the answer to that is we're gonna go and right-click on one of them,
• 01:31 anyone of them.
• 01:32 The only thing we're trying to do is get into the Power Query editor.
• 01:36 When we get in here, again of course, we can expand the queries pane on
• 01:40 the left-hand side and we can see all the queries.
• 01:42 But these also give us no indication of where these queries are going.
• 01:45 As a matter of fact, this gives us even less information than Excel because it
• 01:49 doesn't tell us where these queries are loaded to.
• 01:53 So how do I see what's being done?
• 01:54 How do I see what's actually working here and how they tie together?
• 01:59 In order to do that, we're gonna go to the view tab.
• 02:03 And on the view tab, in every version of Power Query except for
• 02:07 Excel non-subscription 2016 versions, so if you're on Excel 2016 without
• 02:12 subscription you won't have this button, everybody else will.
• 02:15 When you click this, it'll actually open up a dependency tracer for your queries.
• 02:22 In really large models with lots of tables,
• 02:24 this is gonna be a lot smaller set of boxes obviously.
• 02:28 In smaller models, it'll actually look pretty big.
• 02:32 If you go and you click the maximize button,
• 02:34 it'll actually fill this up to a full screen, so you can see a lot more.
• 02:38 And, if you make use of the fit to screen button here, it will actually scale
• 02:43 the entire model, no matter how big or how small, to fit into a single screen.
• 02:47 So, this one makes it a little bit bigger.
• 02:49 Now, I'm gonna go and shrink this down just a little bit,
• 02:51 because I don't need to see it that massive.
• 02:54 And then I'm gonna click fit to screen to reset the size here as well.
• 02:59 We also have the ability to scale, left and right, to make it bigger or
• 03:02 smaller depending on what we want.
• 03:04 Again I'm gonna go back and fit to screen again.
• 03:07 And we have the ability to change the layout view.
• 03:10 Right now, this is reading as a top to bottom model.
• 03:13 But we could change this to say,
• 03:15 I'd like to go from bottom to top which will put the current workbook at
• 03:18 the bottom and it'll build up to the loaded worksheets at the top.
• 03:22 We could also go and say, let's go left to right or maybe right to left.
• 03:28 Whichever way you prefer to see this,
• 03:30 you can actually see it laid out that direction.
• 03:32 I'm gonna go back to the default right now cuz I wanna show how this works.
• 03:37 You can see that we have a current workbook.
• 03:39 The transactions and COA tables are pulling from the current workbook and
• 03:43 they're not loaded.
• 03:43 In other words they're connection only queries.
• 03:47 These guys feed into the left anti join and also the right anti join.
• 03:51 The COA table feeds into the right anti and also the left anti.
• 03:55 We don't know about the order.
• 03:57 We don't know which one came first and which one was merged in second, but
• 04:00 we can see that there are relationships.
• 04:02 Now you can see this can get very, very complicated and
• 04:07 with lots of tables and lots of interdependencies,
• 04:09 it can actually end up looking like a massive plate of spaghetti.
• 04:12 So you think well, no problem, can I trace one of the paths through?
• 04:16 Well sure, let's go take a look at transactions,
• 04:18 we're gonna left click on this guy.
• 04:21 And it says, these are all the queries that either feed into or
• 04:24 feed out of this particular transactions query.
• 04:27 So it comes from the current workbook, it feeds into the left anti and
• 04:31 rIght anti, and both of those guys feed into the full anti join.
• 04:35 If I look at COA, you'll notice that it does a similar path,
• 04:38 it just skips the transactions table.
• 04:41 If I click on Left Anti, it's gonna tell us that it's fed by the transaction and
• 04:45 COA tables which come from the workbook, and it feeds into the full anti join.
• 04:50 If I click on Full Anti,
• 04:52 it highlights everything because everything is a precedent.
• 04:55 So it's kind of nice that we can actually quickly pick up what the precedents are.
• 04:58 One of the challenges, though that you might think is,
• 05:00 well can I click on the arrows to see just a specific dependency?
• 05:03 And, no, unfortunately, you can't..
• 05:05 Which is unfortunate because in really complicated models,
• 05:08 that would be very helpful.
• 05:09 The other thing that you can't do, is you can't move the boxes.
• 05:12 So I'm gonna left click and drag transactions here, and
• 05:15 you'll notice that it moves the entire model, which is also unfortunate.
• 05:18 Because when you get into really complex spaghetti code models,
• 05:22 it would be super useful to be able to move one box and
• 05:25 have it retrace the arrows so that you could isolate what's going on.
• 05:28 But unfortunately, that's not a starter for you either.
• 05:31 One other drawback to this guy, you can't print it.
• 05:34 There's no print button so if you do need to print this out to start drawing and
• 05:38 tracing things, unfortunately you're gonna need a screen capture software.
• 05:41 Use the Windows snipping tool, so you can just type into Windows and
• 05:44 type snip, that'll bring it up so you can take a screenshot and print it,
• 05:47 cuz that's the only facility you have at this time.

Lesson notes are only available for subscribers.