Power Query: Reliable References

Welcome to our Power Query blog. Following on from last week’s look at duplicating and copying queries, today the topic considers referencing queries.

Going back to the example I used last week, I will take a different path.

Requests for two new reports have come in. Both new reports use this information, but for one report I need to do some further grouping and for the second report I need to link to other tables to bring in more data. It’s very important that the two new reports use consistent data. The answer here is to create two copies.

In this case, I want my copy to start with this query, and I only want to make changes to the original query.

From the ‘Manage’ option in the ‘Query’ section of the ‘Home’ tab, I can choose to create a reference query.

My new query has only one step: Source.  In the formula window, I can see that the source is pointing at my original query.

Anything I add to this query will have no impact on my source query.  This is the reason for the attachment icon next to the ‘Reference’ option: I am attaching my original query to my new query.

If I have created a number of reference queries, it can be useful to check the ‘Query Dependencies’ window, which is available on the ‘View’ tab, viz.

(Please note I’ve renamed Order_Charges to OC on the longer names of the queries so I can see the whole name.)

The ‘Query Dependencies’ window shows that whilst ACCT_OC_with_Group_copy is not dependent on ACCT_OC_with_Group since it is a duplicate, ACCT_OC_with_Group_ref and ACCT_OC_with_Group_ref2 are dependent because they have been created as reference queries.

I can also see that ACCT_OC_with_Group is dependent on Items and ‘ACCT_Order_Charges’.

If I look at the Source step for ACCT_OC_with_Group, I can see that is comes from a merge between Items and ACCT_Order_Charges.

This shows that merging creates a reference file, which stops me from changing the original tables when I create new steps.  Appending queries as new will also create a reference query for similar reasons.

Reference queries are useful whenever I want to preserve my original query, but I also need to make sure that my new query is kept in line with the original query.

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