Power Query: Viewing Query Dependencies

Welcome to our Power Query blog. Today, we look at the Power Query Dependencies Viewer.

Workbooks which are built using multiple queries can become complex, and the Power Query Dependencies Viewer is a tool which allows an overview of how each query has been built.

I will revisit the workbook I created for Two (Queries) Become One, where I merged some tables from my Access database:

I double click on ‘ACCT_Order_Charges_with_Group’ to edit my query. In fact, the Power Query Dependencies Viewer includes all dependencies in the workbook, so I could have used any query for this.

In the Query Editor, I choose the ‘View’ tab.

On the right there is a section on ‘Dependencies’, where I can choose the ‘Query Dependencies’ button.

Now admittedly, I haven’t chosen the most complicated model to use for my example, because I want to keep things simple. I have the option to expand the window to use the full screen, and at the bottom of the window is a scaling option, which is more useful for large complex models. There is also a ‘Layout’ dropdown.

The ‘Layout’ default is ‘Top to Bottom’ which works well for my model. I can also try ‘Left to Right’:

The icon on the right also allows me to ‘Fit to Screen’, which is useful for complex models, particularly as there is no print option! If I want to concentrate on a particular component, right-clicking on that component allows me two more options:

I can make my component the centre of my window (‘Center to View’), or make the hierarchy associated with that component central to my view (‘Center Hierarchy to View’).

Many of these features have been added because I can’t do what I would intuitively like to do which is to click and drag parts of my model to display as I wish. I can click and drag, but the whole model moves as one intact unit.

One nice feature, again very useful in more complex models, is that when I click a component, the associated components are highlighted. In the screen below, I clicked on ‘ACCT_Order_Charges’:

As there is no direct connection with ‘Items’, that component is greyed out. To show the different options that can appear for each component, I add an unrelated query which I will not load to my model.

When I view the ‘Power Query Dependencies’ now, I see the following:

So, my components are either ‘Loaded to Data Model’, ‘Loaded to worksheet’ or ‘Not loaded’. ‘Not Loaded’ means that when I created the query, I opted to make it ‘connection only’ and not load it into the data model. Note that since I have extracted the queries from different sources, the source paths are shown distinctly on the diagram.

However, ‘Not Loaded’ can be confusing. Consider the diagram below:

I have gone back to the query pane in the Excel worksheet and set the query ‘Items’ not to be loaded to the data model. It is still part of the ‘ACCT_Order_Charges_with_Groups’ query, so it is part of the data loaded to the worksheet.

In summary, the Power Query Dependencies Viewer is useful to get an overview of what is going on in a workbook that uses queries, but I recommend saving before removing any queries that may appear to be ‘redundant’!

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!