Power BI SQL Query
For this tutorial, we are going to delve deeper and understand how Query Dependencies work in Power BI.
Query Dependencies show you how the queries are linked together inside Power BI. You can see which queries depend on other queries or tables. This is useful when you plan to do a lot of data transformations inside your model.
This feature has been part of Power BI SQL Query for a while now, but not many users know how to maximize its function. If you often work with complicated Power BI setups, you need to understand Query Dependencies more.
Understanding Query Dependencies Inside Power BI
To get started, click on the View ribbon, and then click Query Dependencies.
The first thing that you can see in the dependency tree is the source folder of the initial query.
Under the initial query, you can see that it branched out into several other queries. You have the Customers, Products, Regions, and Sales table queries. Just by looking at this, you already have an overview of how these queries are connected.
I’ll show you an example of how query dependencies work inside Power BI. For instance, we already have our Channel column and Channel Code column.
What I want to do is create another table of this. I want to break out this information and put it into another table.
This is a good idea because as you can see in these columns, there’s a lot of repetitive information, especially when you have multiple columns containing similar things.
In theory, we can just have the Channel and Channel Code columns in another table by themselves, and then draw a relationship between the Channel table down to our Sales table.
To do that, right-click the Sales table, and then select Reference.
Next, rename the duplicated sales query from Sales (2) into the Channels table. In the new Channels table, click on the Channel column, hold down the Ctrl key, and click the Channel Code column.
While still pressing Ctrl, right-click the said columns, and then select Remove Other Columns.
After that, only the Channel and Channel Code columns will remain in the Channels table.
Now, you need to create a lookup table for all your channels using the Channels table. But first, you need to remove the duplicate entries by right-clicking and selecting Remove Duplicates. This is an efficient way to create your channel codes.
As you can see, only three entries are left in both the columns. Now, click the Add Column ribbon and then select Column From Examples. You need to do this so you can abbreviate your channel codes using the first letter.
By doing this, you can create a new column and add the Channel Short Code table.
If you look back into the Query Dependencies view, you can now see the connection between the Sales table query and the Channels table.
***** Related Links *****
Customer Segmentation Techniques Using The Data Model – Power BI & DAX
How the DAX Calculation Engine Works
Showing Results Before Or After A Selected Date Using DAX
Conclusion
This example for query dependencies is just a simple one. Once you have a lot of data from various sources, the dependency tree will look more complicated.
As I mentioned, this technique is helpful when there’s a lot of repetitive information in several columns. Just make sure the new table you have created is still connected and dependent on the initial query.
I hope you have learned a lot from this tutorial. There are various opportunities to use Query Dependencies. The key is to practice and experiment with different examples in varying scenarios.
Until next time!
Sam