1

Fairly new to power queries and finding my feet largely by trial and error.

I have build a master query returning ~ 2000 rows of data covering different regions. I want to create sub reports on different tabs for each region. I can easily do this by copying the original table and applying a filter on region for each new query. As my s/s is already 10mb, I am trying to do this is as efficiently as possible in terms of performance. I understand I can do this also by creating a "reference only" to the master query instead of duplicating and filtering master query (so creating 10 versions of master query with different filters).

I have been trying to do this via Query / Reference menu but not sure if I end up with a "connection only" query as it doesn't say it in the Queries panel on the right.

Anyway, I guess the questions are: 1. What is the difference between queries and "connection only" queries (especially with regard to performance / spreadsheet size)? 2. When is best to use "connection only" query? 3. How to create "connection only" query (ideally via menu not code) and how to check if a query is connection only?

enter image description here

Derek Pollard
  • 6,953
  • 6
  • 39
  • 59
user11345588
  • 69
  • 1
  • 2
  • 8
  • I'm not sure if there will be a difference in the answers depending on whether or not loading to a data model is being discussed as a possibility. Are you also loading some data to a data model? (And if you don't know, then no, you almost definitely aren't.) – Mistella Apr 30 '19 at 16:09

1 Answers1

2

Connection only signifies that the data is not being materialised anywhere. It may still be referenced by other queries, but the data isn't being loaded to a worksheet table or to the data model.

You can control the behaviour of queries with the Load To dialog, invoked by Close and Load To from the Query Editor, or right click > Load To from the Workbook Queries pane.

enter image description here

Connection only queries reduce workbook size - good rule of thumb is not to materialise any queries unless you really need to. In your example, it looks like you want to materialise each regional query, but unless you also need a master table of all regions, then your 'master' query may be connection only.

In performance and size terms, it makes more sense to only have your master query, loaded to the data model, with a regional slicer on your report...

Olly
  • 7,749
  • 1
  • 19
  • 38