Power Query: Questioning Queries

Welcome to our Power Query blog.  This week, I suggest some tips when encountering a query model for the first time.

Mary, my favourite imaginary salesperson, has been dabbling in Power Query.  She has constructed some queries to solve a problem, but now she is on a well-earned break, and I have to investigate what it does:

These are my three [3] top tips when looking at someone else’s query model.  Note that this is quite a simple model, which I have chosen so that the screen shots will include all the information.  The same principles apply to much larger models.

Tip 1: Admire the View

I find the best way to get a feel for a collection of queries is to head to the View tab and use the ‘Query Dependencies’ button:

This gives me a diagram, allowing me to work out the relationships between the queries:

This tells me where the data comes from, which queries are loaded to the workbook, and which queries are connected.  As the name suggests, the dependencies are shown too.  I can adjust the size of the diagram and choose to maximise the window if I wish, by using the controls at the bottom right.  I can also change the way the data appears by choosing a different Layout:

I notice that the function fx_Rental is not dependent on a query, which brings me to my next tip…

Tip 2: Viewing Functions

If I view this function, I cannot see the steps:

This would not be an issue if I had an underlying query which had been converted to a function, but you may still come across standalone functions like this.  I can either use the ‘Advanced Editor’ from the Home tab to view the steps

or, if want to see the steps in the ‘APPLIED STEPS’ pane, I can take a duplicate copy of the function:

Using the copied query, I can remove the features that make it a function (the highlighted lines below):

I check I have no errors:

Now I can look at the steps individually:

Tip 3: Use a Subset of Data

Perhaps the best way to check what a query does is to actually run it.  However, this can be time-consuming with large data sets.  One way to speed things up, is to run a query on a subset of data.  I looked at the ‘Query Dependencies’ in the first tip, and I can use this to determine the base query, in this case Events preGroup.  Reducing the rows returned by this query will affect all the other queries too, speeding up the model.

If I go to the ‘Source’ step, I can insert a step to reduce the data:

Obviously, for this query this step would not be necessary, but I am showing the principle.  If I choose to ‘Keep Rows’ from the Home tab, I can control the amount of data processed:

In a large model, I might choose a value of 1000, but here, I will choose the top two [2] rows.  When I try to enter the step I will get a warning, which I can ignore:

I continue, and enter the step:

This reduces the data, and the time to process the query.

I hope these tips help next time you are presented with someone else’s query!

Come back next time for more ways to use Power Query!