Trick! See both “detailed” and “big picture” view of your queries in ONE place.


Every Power BI developer who has used Power Query in Power BI desktop for data wrangling would have sailed through this issue.

I can use “Query dependencies” to see the “big picture” view of how my queries are related, but I can’t see the “detailed” view of queries inside “Query dependencies“.

– or –

I can’t quickly understand how data is extracted and transformed across queries.

In this example, we will start with Power Query editor in Power BI desktop. There are three queries – Target 2010-2011, TargetQuantity and Target, and one parameter.

Queries pane in Power Query editor within Power BI desktop

Select the Query dependencies feature in View tab of Power Query editor.

The query dependencies display the names of the queries and parameters with their dependencies. Selecting a parameter or a query highlights its related items

Query dependencies in Power Query editor within Power BI desktop

What “Query dependencies” lacks is that we can’t expand the queries to see the individual steps. So, I need to close the “Query dependencies” window and switch to the query to see its steps.

This kills our time as we need to switch back and forth between query dependencies window and the query.

Don’t worry! You can use the below trick to see both the “big picture” view and “detailed” view of the steps in Power BI service.

Answer: Dataflows!

Create a dataflow in your app workspace with a blank query or blank table. This blank query/table can be deleted later as it doesn’t serve any purpose in this scenario.

Dataflow

Multi-select the groups or individual queries in Power Query editor within Power BI desktop. Copy it (Ctrl + C)!

Back to dataflows. Paste the copied items (Ctrl + V)!

Power Query editor in Power BI dataflow

It is usual to see errors against these pasted queries as it references on-premises data sources for which gateway is not configured! Just ignore these errors.

Select the diagram view in View tab or Diagram view. This view looks quite like query dependencies in Power BI desktop, but it is not!

You can expand/collapse the queries …

You can highlight related queries …

You can author new queries as well as do step level actions …

Conclusion:

Until Power Query within Power BI Desktop is upgraded with the above feature, just copy, and paste your queries to a dataflow to take advantage of the Diagram view feature.

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s