Not every Power Query query should load into your data model — but most beginners load all of them and pay the price in bloated files and duplicate data. Learn how to deliberately control which queries load and which stay Connection Only, and why that choice shapes the performance and clarity of your entire data pipeline.

You've built a beautiful chain of queries in Power Query. There's a raw data pull, a cleaned version, a lookup table, maybe a merge that joins two of them together. You click "Close & Apply" and Excel or Power BI dutifully loads every single one of them — including the raw, unfiltered staging query you only created as a stepping stone. Now you have duplicate data clogging your workbook, your file size has ballooned, and your colleagues are confused about which table to actually use in their pivot tables.
This is one of the most common and fixable problems Power Query beginners run into, and it comes down to a single concept: not every query needs to load into your data model. Some queries exist purely to serve other queries. Others are your final, polished outputs. Knowing the difference — and knowing how to tell Power Query which is which — is the skill that separates organized, efficient data workflows from chaotic tangles of redundant tables.
By the end of this lesson, you'll understand what "loading to the data model" actually means, what "Connection Only" mode does and why it exists, and how to deliberately control which queries land in your workbook or report. You'll also understand the performance and maintenance implications of getting this right.
What you'll learn:
This lesson assumes you've used Power Query at least once — you know how to open the editor, create a query from a data source, and apply basic transformations. If you're brand new, start with Getting Started with Power Query: Master Connect, Transform, Load for Real-World Data before continuing here.
Before we can talk about controlling load behavior, we need to be precise about what "loading" does.
When Power Query finishes evaluating a query, it has a result — typically a table of rows and columns. Loading means taking that result and materializing it somewhere so that the rest of your tool (Excel, Power BI) can see and use it. Where it gets materialized determines what you can do with it.
In Excel, a query can load to:
In Power BI, the distinction is simpler but equivalent:
Key insight
Connection Only doesn't mean the query doesn't run. It means the results aren't stored anywhere independently. When a downstream query that does load references a Connection Only query, the M engine evaluates that upstream query as part of calculating the downstream result.
Here's a realistic scenario to make this concrete. Imagine you work in sales operations and your source data is a CSV export from your CRM — a file with 50,000 rows of opportunity records. Your final deliverable is two things: a summary table of revenue by region, and a detail table of open opportunities only.
A natural way to build this in Power Query is:
Raw_Opportunities — the initial connection to the CSV, no transformations, just raw dataCleaned_Opportunities — built on top of Raw_Opportunities, with column renames, type corrections, and null handlingOpen_Opportunities — built on top of Cleaned_Opportunities, filtered to Stage <> "Closed Won" and Stage <> "Closed Lost"Revenue_By_Region — built on top of Cleaned_Opportunities, grouped and aggregated by regionQueries 3 and 4 are your outputs — the tables you'll actually use. Queries 1 and 2 are plumbing. If you load all four to the data model, you've materialized your raw 50,000-row CSV twice unnecessarily (once as Raw_Opportunities, once as Cleaned_Opportunities), when only the two output queries needed to exist as tables.
This is the staging query problem. The fix is to set Raw_Opportunities and Cleaned_Opportunities to Connection Only.
Tip
Think of Connection Only queries the way a chef thinks about mise en place — the prepped ingredients. They're essential preparation, but you don't serve the chopped onions directly to the customer. Only the finished dish goes to the table.
In Excel, you have two moments when you can control load behavior: when you first load a query, and after the fact in the Queries & Connections pane.
When you're in the Power Query editor and click Close & Load, Excel will show you a dialog. Instead of clicking Close & Load directly, click the dropdown arrow next to it and choose Close & Load To...
A dialog box called "Import Data" appears. Here you'll see options:
If you want Connection Only, select "Only Create Connection." If you want it in the Data Model (for Power Pivot and DAX), leave it at Table or select your preferred option and check the "Add this data to the Data Model" checkbox.
If a query is already loaded and you want to change it, right-click the query name in the Queries & Connections pane (you can open this pane from the Data tab → Queries & Connections) and choose Load To...
The same "Import Data" dialog appears, and you can switch between options. Changing from Table to Connection Only will delete the worksheet table. Changing from Connection Only to Table will create a new one.
Warning
If you delete a worksheet table by switching to Connection Only, any Excel formulas that referenced cells in that table will break. Always check for downstream dependencies before changing load settings on a query that's been in use.
Power BI's approach is slightly different and, in some ways, more streamlined.
Inside the Power Query editor, right-click any query in the Queries pane on the left side. You'll see a toggle called Enable Load. When this is checked, the query will load to the data model. When unchecked, it becomes Connection Only — it won't appear as a table in the Fields pane.
You can also select multiple queries at once (hold Ctrl and click each one), right-click, and toggle Enable Load for all of them simultaneously. This is very useful when you're setting up a new multi-query pipeline and want to disable loading for a whole group of staging queries at once.
Queries with Enable Load turned off appear in the Queries pane with their name in italics. Queries with Enable Load turned on appear in regular (non-italic) font. This is an at-a-glance signal to you and anyone else maintaining the file about which queries are "live" outputs versus supporting infrastructure.
Tip
Make a habit of checking for italicized query names whenever you open someone else's Power BI file. The pattern of what's enabled versus disabled tells you a lot about how the data model was designed.
It's easy to think of Connection Only queries as dormant or inactive, but that misunderstands their role. They are fully evaluated every time a downstream query that references them is refreshed.
Think of it this way: Cleaned_Opportunities being Connection Only doesn't mean it gets skipped. It means its results don't get saved anywhere on their own. But when Open_Opportunities refreshes, the M engine evaluates Cleaned_Opportunities as part of that evaluation. And when Revenue_By_Region refreshes, it evaluates Cleaned_Opportunities again.
This is both a feature and something to be aware of. It's a feature because it means you have one centralized cleaning logic (in Cleaned_Opportunities) that feeds multiple outputs without having to duplicate that logic. It's something to be aware of because it means that if Cleaned_Opportunities is expensive to compute, that cost is paid multiple times during a refresh.
This becomes especially relevant when you're working with combining data from multiple sources with Append and Merge — the base queries that feed into your merges and appends are natural candidates for Connection Only, since their purpose is to serve the merge result, not to be standalone tables.
Key insight
Connection Only is about where the output lives, not about whether the query runs. The M engine will still evaluate your Connection Only queries whenever they're needed by a downstream query that does load.
Now that you understand the mechanics, let's talk about design. A well-structured Power Query project has a clear layered architecture:
Layer 1: Raw / Source queries — Connect directly to data sources. Minimal or no transformations. Always Connection Only. Their job is to hold the connection definition so the rest of the pipeline doesn't have to.
Layer 2: Staging / Cleaning queries — Built on top of Layer 1. Apply type corrections, column renames, null handling, deduplication. Almost always Connection Only. These are the backbone of your cleaning logic as described in Cleaning Messy Data with Power Query: Nulls, Errors, and Type Conversions.
Layer 3: Shape / Enrich queries — Merges, appends, group-bys, custom column logic. May be Connection Only if they feed further downstream, or may be final outputs.
Layer 4: Output queries — The tables that actually load. These are the ones your end users, your DAX measures, or your report visuals interact with. Enable Load is ON for these.
This layered thinking maps directly to the multi-stage staging architectures described in Building Multi-Stage Staging Architectures in Power Query.
One practical technique is to use prefixes in query names to signal their layer and load status:
src_ or raw_ — source/raw queries, Connection Onlystg_ — staging/cleaning queries, Connection Onlydim_ or fact_ — output tables that load to the data modelWhen you open the Queries pane, your pipeline structure becomes immediately readable. Anyone maintaining the file can tell at a glance what's plumbing and what's output.
This isn't just about organization — it has real performance implications.
Every query that loads to the data model consumes memory and disk space. In Excel, this means workbook file size. In Power BI, this means .pbix file size and in-memory model size. If you load your raw 50,000-row CSV and your cleaned 50,000-row version and your two output tables, you've stored roughly four copies of the same data (or large portions of it). That's unnecessary weight.
Setting intermediate queries to Connection Only eliminates their contribution to file size entirely. Only the final, loaded tables occupy space in the data model. For large datasets, this difference can be dramatic — files that were 80MB can drop to 20MB simply by setting the right queries to Connection Only.
Refresh time is a more nuanced story. As noted earlier, Connection Only queries still run during refresh. But you do avoid the overhead of writing their results to the data model, which for large queries involves column store compression and memory allocation. The net effect is usually faster refresh for Connection Only intermediates compared to loading them.
Warning
Don't assume that more loaded tables means more reliable data. It actually means more chances for data to be stale, out of sync, or duplicated in confusing ways. Fewer loaded tables with cleaner lineage is almost always better.
Let's build this from scratch so you can practice the full workflow. This exercise works in both Excel and Power BI Desktop.
Setup: Create a simple CSV file on your machine called sales_data.csv with these columns: OrderID, Customer, Region, Product, Amount, Status. Add about 15–20 rows of made-up data, with Status values being either "Open", "Closed", or "Cancelled."
Step 1: Create the Raw query
Open Excel or Power BI. Connect to your CSV file via Get Data → From File → Text/CSV. In the Power Query editor, do absolutely nothing to the data — just rename this query raw_Sales. Then right-click it and set it to Connection Only (Excel: Close & Load To → Only Create Connection; Power BI: uncheck Enable Load).
Step 2: Create the Staging query
Right-click raw_Sales in the Queries pane and choose Reference. This creates a new query based on raw_Sales. Rename this query stg_Sales. Apply these transformations: set correct data types on all columns, rename any awkward column names, and use filtering techniques to remove any rows where Amount is null or zero. Set stg_Sales to Connection Only.
Step 3: Create two output queries
Right-click stg_Sales and choose Reference. Rename this query Open_Orders. Filter it to only rows where Status equals "Open". Leave Enable Load ON (this one should load).
Right-click stg_Sales and choose Reference again. Rename this query Revenue_By_Region. Use the Group By feature (Transform tab → Group By) to group by Region and sum the Amount column. Leave Enable Load ON.
Step 4: Close & Apply
Click Close & Apply (or Close & Load). Verify that only Open_Orders and Revenue_By_Region appear as tables in your data model or as worksheet tables. The raw_Sales and stg_Sales queries should be visible in the Queries pane but should not have created any tables.
Step 5: Verify
In Excel, open the Queries & Connections pane. You should see all four queries listed. raw_Sales and stg_Sales should show "Connection only" under their names. In Power BI, check the Fields pane — you should see only Open_Orders and Revenue_By_Region.
"I changed a query to Connection Only but I can see it still uses memory." Remember — Connection Only doesn't mean the query doesn't execute. It means it doesn't materialize independently. The memory you're seeing is from the downstream queries that reference it, not from the Connection Only query itself storing a copy.
"My output query suddenly stopped working after I set an upstream query to Connection Only." This shouldn't happen — Connection Only queries are still fully referenceable. If your output query broke, the more likely culprits are: a name change on the upstream query, a column rename, or a step that was accidentally deleted. Check the Applied Steps of the output query for error indicators.
"Power BI says my query has a circular reference." This can happen when queries reference each other in loops. It's unrelated to Connection Only settings — it's a query dependency issue. Check your query references and make sure data flows in one direction, from source to output. The article on understanding query dependencies and evaluation order goes deep on this.
"I want to preview a Connection Only query in Excel, but I can't find the data." Right. That's exactly what Connection Only means — there's no table to look at in the workbook. To inspect the data, go to the Power Query editor and click on the query name. The preview pane will show you the results without loading them.
"Should I ever load a staging query to the data model?" Sometimes yes. If a staging table is used by many downstream queries and is expensive to compute (say, it's pulling from a slow API or doing a complex merge), you might load it deliberately to avoid re-evaluating it multiple times during refresh. This is an intentional performance trade-off. See Power Query Performance: Master Folding, Buffering & Optimization Techniques for a thorough treatment of these scenarios.
Note
In Power BI, there's also a distinction between queries that load to the model and queries that are used purely for parameters or configuration. These parameter queries are always Connection Only by nature, since they don't produce tables — they produce scalar values used to drive other queries. If you're working with parameterized queries and dynamic data sources, you'll see this in action.
The difference between loading a query to the data model and setting it to Connection Only is the difference between having a clean, efficient data pipeline and having a cluttered, bloated one. Here's what to carry forward:
As you build more complex pipelines, this discipline of intentional load control becomes increasingly important. When you start combining files from folders, working with slowly-changing dimensions, or building out full staging architectures, knowing which queries should live in memory and which should remain as pure logic is the foundation of a maintainable, performant solution.
Explore the Power Query Best Practices for Maintainable ETL guide as your next stop — it builds directly on the concepts here and will show you how load control fits into the broader discipline of writing Power Query solutions that hold up over time.