Staging query is getting executed for every sub query

Hi, I think I understand. You're point comes from the perspective of what M language does and how the compiler interprets duplicate vs reference. 

My perspective comes from SQL DB optimization and reducing the total number of queries to the database. As over the wire latency maters much more than processor time.

What I found is... that Power Query does in fact send multiple SQL queries per sub query when the stagging query is set to 'Connection Only'.

Proof / Repro

1. Set your stagging query to 'Connection only' as Ken recomended.
2. Use a SQL connection or some other remote data source. I used SQL for this test (and easier to test as you'll see).
3. Create multiple sub queries as 'reference' queries. It actually doesn't matter what these queries do.
4. Open up network monitor of your choice. I used WireShark. Set your capture filter to your remote destination.
5. Hit Refresh all in your Excel spreadsheet and inspect the wire.

I have detected exactly the same number of SQL Select statements hit the wire as there are sub queries.

Potential Workaround Found

1. Set your stagging query to 'Load to Table'
2. Create a new query that loads from the table you just created
   let
       Source = Excel.CurrentWorkbook(){[Name="yourTableNameHere"]}[Content]
   in
       Source
3. Set your sub queries to 'reference' the newly created query in step 2.
4. Setup WireShark as before, and Hit Refresh All

Only one SQL query will hit the wire and all other sub queries will now wait for completion.

Further Discusion

Ideally, resultant set should be used, but M Language cannot tell where the data comes from. Is it being cached, is it dynamically created, or is it coming from a remote system? Without that, M Language cannot effectively provide step optimization techniques as claimed with 'duplicate' vs 'reference'. The combination of missunderstanding of the capabilities with M Language optimziation and bad architectual advice lead me to this state. Stanging Query concept only works if you 'cache' the resultant set somewhere. Either in the DataModel or in a table.