Locked lesson.
About this lesson
If you ever need to audit your models, you'll find this tool can be helpful. It shows how the queries link together and flow from one to the other.
Exercise files
There are no related exercise files for this lesson.
Quick reference
Viewing Query Dependencies
An overview of the Query Dependencies Viewer.
When to use
When you need to audit a file to see where and how various queries within it are being used.
Instructions
Activating the query dependencies viewer
- Open the Query Editor by editing any query in the workbook
- Go to the View tab --> Query Dependencies
- The Query Dependencies window will open, depicting the connections between the various queries
- The connections between the various tables are indicated with arrows
Using the query dependencies viewer
- Click any query box to highlight all of its precedent and dependent queries
- Scale the interface to show all the queries by using the Fit To Screen button in the bottom right
- Use the Layout button to adjust the model layout to show in different ways (left to right, right to left, top to bottom, etc…)
Query dependencies viewer shortcomings
- Tasks that cannot be performed in the Query Dependencies viewer
- Select arrows to trace their connections
- Individually move the boxes that represent each query
- Show relationships one connection at a time
- Print the view
Hints & tips
- The Query Dependencies viewer is available in:
- Excel 2010 and 2013
- Subscription versions of Excel 2016 (may not be in non-subscription versions)
- Power BI Desktop
- To print the Query Dependencies view, use the Windows Snipping Tool to take a screenshot (which can then be printed)
- Press the Windows key on your keyboard --> type “Snipping” to locate the snipping tool
- 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.