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/