Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Power Query

Promoting Queries to the Data Model vs. Connection Only in Power Query: Controlling What Loads and Why

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.

🌱 Foundation16 min readNov 17, 2026Updated Nov 17, 2026
Promoting Queries to the Data Model vs. Connection Only in Power Query: Controlling What Loads and Why
On this page
  • Introduction
  • Prerequisites
  • What "Loading" Actually Means
  • The Staging Query Problem
  • How to Change Load Settings in Excel
  • At Initial Load
  • After the Fact
  • How to Change Load Settings in Power BI
  • In the Power Query Editor
  • Visual Indicator
  • Why Connection Only Queries Still Matter
  • Designing a Multi-Query Pipeline Intentionally
  • A Naming Convention That Reflects Load Behavior
  • The File Size and Performance Impact
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps
  • Promoting Queries to the Data Model vs. Connection Only in Power Query: Controlling What Loads and Why

    Introduction

    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:

    • The difference between loading a query to the data model (or a worksheet) versus setting it to Connection Only
    • Why staging and helper queries should almost always be Connection Only
    • How to change a query's load settings in both Excel and Power BI
    • How load settings affect file size, refresh time, and downstream query behavior
    • A practical workflow for designing multi-query pipelines with intentional load destinations

    Prerequisites

    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.


    What "Loading" Actually Means

    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:

    • A worksheet table — the results appear as a formatted Excel table on a sheet, visible to formulas, pivot tables, and users
    • The Data Model (Power Pivot) — the results go into an in-memory columnar store that supports relationships, DAX measures, and large datasets efficiently
    • Connection Only — the query is defined and can be referenced by other queries, but its results are not materialized anywhere in the workbook

    In Power BI, the distinction is simpler but equivalent:

    • Load enabled — the query's output table is added to the report's data model, where you can build visuals, write DAX, and create relationships
    • Load disabled (Connection Only) — the query exists in Power Query and other queries can reference it, but it doesn't appear as a table in the Fields pane

    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.


    The Staging Query Problem

    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:

    1. Raw_Opportunities — the initial connection to the CSV, no transformations, just raw data
    2. Cleaned_Opportunities — built on top of Raw_Opportunities, with column renames, type corrections, and null handling
    3. Open_Opportunities — built on top of Cleaned_Opportunities, filtered to Stage <> "Closed Won" and Stage <> "Closed Lost"
    4. Revenue_By_Region — built on top of Cleaned_Opportunities, grouped and aggregated by region

    Queries 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.


    How to Change Load Settings in Excel

    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.

    At Initial Load

    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:

    • Table — loads to a worksheet as a formatted table
    • PivotTable Report — loads and creates a pivot table
    • PivotChart — same but with a chart
    • Only Create Connection — this is the Connection Only setting
    • Add this data to the Data Model — a checkbox at the bottom

    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.

    After the Fact

    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.


    How to Change Load Settings in Power BI

    Power BI's approach is slightly different and, in some ways, more streamlined.

    In the Power Query Editor

    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.

    Visual Indicator

    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.


    Why Connection Only Queries Still Matter

    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.


    Designing a Multi-Query Pipeline Intentionally

    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.

    A Naming Convention That Reflects Load Behavior

    One practical technique is to use prefixes in query names to signal their layer and load status:

    • src_ or raw_ — source/raw queries, Connection Only
    • stg_ — staging/cleaning queries, Connection Only
    • dim_ or fact_ — output tables that load to the data model

    When 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.


    The File Size and Performance Impact

    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.


    Hands-On Exercise

    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.


    Common Mistakes & Troubleshooting

    "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.


    Summary & Next Steps

    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:

    • Connection Only queries are full citizens of your query pipeline — they transform data, serve as references for downstream queries, and must be maintained like any other query. They just don't produce standalone tables.
    • Every intermediate or staging query in a multi-step pipeline should default to Connection Only unless you have a specific reason to load it.
    • Only your final output queries — the tables your users, visuals, or DAX measures will actually consume — should have loading enabled.
    • The italics convention in Power BI and the "Connection only" label in Excel give you instant visual feedback about your pipeline's load structure.

    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.

    Work With Us

    From insight to implementation

    Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

    Let's Build

    Power Query Essentials

    Previous

    Implementing Custom Locale-Aware Number and Date Parsing Pipelines in Power Query M: Resolving Regional Format Conflicts When Combining Multi-Country Data Sources

    Related Insights

    Power QueryFoundation

    Understanding Value Types and Primitive Operations in M: Numbers, Text, Logical, and Null Foundations

    16 min
    Power QueryExpert

    Implementing Custom Connection Retry and Backoff Logic in Power Query M: Building Fault-Tolerant Data Source Connectors for Unreliable Networks and Throttled APIs

    28 min
    Power QueryExpert

    Implementing Custom Locale-Aware Number and Date Parsing Pipelines in Power Query M: Resolving Regional Format Conflicts When Combining Multi-Country Data Sources

    26 min

    On this page

    • Introduction
    • Prerequisites
    • What "Loading" Actually Means
    • The Staging Query Problem
    • How to Change Load Settings in Excel
    • At Initial Load
    • After the Fact
    • How to Change Load Settings in Power BI
    • In the Power Query Editor
    • Visual Indicator
    • Why Connection Only Queries Still Matter
    • Designing a Multi-Query Pipeline Intentionally
    • A Naming Convention That Reflects Load Behavior
    • The File Size and Performance Impact
    • Hands-On Exercise
    • Common Mistakes & Troubleshooting
    • Summary & Next Steps