I have been on holidays taking a much needed break from work, so it has been very quite on my blog over the last few weeks. But I am back now! I woke up this morning to a very pleasant surprise – Microsoft has delivered the long waited for Query Dependency View in Power BI Desktop.
“What is this and why are you so excited Matt” I hear you ask.
What is Query Dependency View
Simply stated, it is a “table view” of all of your queries showing the linkages between the tables/queries. Now if you are simply loading data directly into the data model with no transformation, then this really doesn’t add any value. However if you are doing a lot of transformation using staging queries etc, then this is a must have tool.
How to Access Query Dependency View
Download and install the latest version of Power BI Desktop. Open a workbook that has data loaded, and go into the edit query mode. Do this via Home, Edit Queries, Edit Queries as shown below.
Then click on View, Query Dependencies.
What it Looks Like in Action
The best way I can demonstrate why I think the Query Dependencies view is required is to show you the Dependencies View from a workbook that I developed for a client last year. As you could probably imagine, this workbook was very difficult to manage without a view like this. What I ended up doing was to develop my own dependencies view using Microsoft Visio. This took quite some time and effort, and thankfully is no longer required.
It is worth pointing out that this workbook was not a reporting tool using Power Pivot/Power BI, but it was an auditing tool. It was used to import data from many spreadsheets, compare this data against master data, and identify data errors and exceptions. This tool was built using lots of interim queries with lots of cross dependencies (as you can see).
The Dependencies Query view is still not available in Power Query for Excel however it is easy to import your workbook into Power BI Desktop to immediately leverage the new feature.
Improvements Still Needed
So this is version 1 of this view, and it is simply great. There are some things that still need improvement in my view, including.
- There is no maximise button for the window.
- You currently can’t reposition the tables on the screen manually
- It could benefit from some user controlled colouring capabilities to highlight different types of tables, such as those that are loaded into the data model etc.
- Deliver it in Power Query for Excel of course.
But having said that, it is still great now so get to it and start using it.
If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/product/power-query-excel-power-bi-online-training/
Been waiting for this one! Especially in Power Query. I’m not able to try it yet, but here are some thoughts. I am thinking about how to use this for application documentation:
– Export the diagram as PDF
– Export the diagram as objects for use in Excel as shapes or in Visio
– Access to the data set used to build the diagram!
– Can we differentiate items which land to tables from those that have no table?
– Can we identify the step in the query where the upstream query connects?
– Can we differentiate easily between external data source queries and internal ones?
I totally agree
Great post, Matt, and I’m glad to see this finally hit the product. Like you said though, it’s definitely a version 1 and I hope they keep going on it.
I just pulled in a massive model (built entirely in Excel) and the query chain is so big that I have to zoom out to 30% on a 22″ widescreen monitor to see all the boxes… and it looks like a plate of spaghetti the way it’s been organized. (To be fair, I’m not sure how you’d display this model any other way!)
I do like that you can click on any step and see what depends upon it. Took me a bit to work out that was what was happening though!
One thing I’d like to see is the ability to click a query and see the queries that rely on that step directly. Maybe shade or colour the arrows that feed out. That’s the primary use I have for this tool is “if I’m going to modify this query, which ones might break?” As it is, it’s great as showing the ENTIRE downstream chain, but I’d like to identify only the IMMEDIATE dependencies, as those are the ones I really need to worry about. (Case in point, I have one table that is called by 3 queries, but long term affects 20. I want to see just those 3 tables to review them, but following the snaking arrows is difficult.)
I agree 100% with need for improvement. I have a workbook …with over 80 queries, and many many dependencies. When I looked at the Dependency View it s skewed horizontally and really unreadable.
I would add a Print capability to your list of improvements.
(Of course I have to load the workbook to Power BI Desktop to view it … will migrate once Query Parameters are available in Excel … and the PivotTable Visual, which I saw reference to in the Roadmap screenshot earlier this week, is available in Desktop! ).
Glad to see you are back!!