Automating Data Refresh and Load Destinations in Power Query: Controlling Output Tables, Connection-Only Queries, and Data Model Targets

Introduction

You've built a solid Power Query pipeline — data comes in from three sources, gets cleaned, merged, and shaped exactly how you need it. Then a colleague opens the file and asks: "Why are there six extra tables in this workbook? And why is this one taking forever to refresh?" The answer almost always comes down to load destinations. Most Power Query users let the defaults do the work, and the defaults are not designed for production pipelines.

Understanding how to deliberately control where your query output lands — a worksheet table, the Data Model (Power Pivot), or nowhere at all (connection-only) — is the difference between a fragile proof-of-concept and a maintainable reporting solution. Get this right and you'll dramatically reduce file size, improve refresh performance, prevent users from accidentally editing intermediate data, and give yourself the architectural foundation to build genuinely scalable ETL workflows.

By the end of this lesson, you'll be able to architect a multi-query Power Query solution with full control over every output. You'll know when to use each load destination, how to switch between them, and how to avoid the gotchas that trip up even experienced practitioners.

What you'll learn:

• The three load destination types in Power Query and when each is appropriate
• How to configure connection-only queries for staging and intermediate transformation layers
• How to load directly to the Data Model vs. worksheet tables, and the performance trade-offs of each
• How to set and change load destinations for existing queries without breaking dependencies
• How to structure a multi-stage query architecture that controls refresh order and output cleanly

---

Prerequisites

You should be comfortable with the basics of connecting to data, applying transformations, and understanding the Power Query editor. If any of that is still fuzzy, Getting Started with Power Query: Master Connect, Transform, Load for Real-World Data is the right starting point. You should also have a working sense of how queries reference each other — if you haven't thought about that, Understanding Query Dependencies and Evaluation Order in Power Query: How the M Engine Executes Your Steps will fill in the gaps.

---

The Three Load Destinations: A Mental Model First

Before touching any settings, it's worth building a clear mental model. When Power Query finishes evaluating a query, that result — a table in memory — has to go somewhere. Power Query gives you three choices:

1. Load to worksheet table — The query result materializes as a formatted Excel Table on a worksheet. Users can see it, sort it, filter it, and (dangerously) accidentally edit it.

2. Load to Data Model — The query result is pushed into Power Pivot's in-memory columnar store (VertiPaq). It's compressed, invisible to casual users, and available for DAX measures and relationships.

3. Connection Only — The query is evaluated and held in memory only during refresh. Nothing is persisted to the workbook. It exists purely so other queries can reference it.

The confusion arises because Power Query has opinions. When you first import a query in Excel, the default is to load to a worksheet table. But if you add that query as a step in another query, Power Query may or may not materialize it — that depends on the evaluation engine, not on your explicit instruction. Understanding this distinction between explicit load destinations (what you configure) and implicit evaluation (what the engine does internally) is the foundation of everything that follows.

---

Load to Worksheet Table: When It's Right and When It Isn't

Loading to a worksheet table is the most familiar destination. It's what most people think of as "the result" of a Power Query transformation. When you click Close & Load, that's where you end up by default.

When worksheet tables make sense

• Final reporting tables that need to be visible to users or consumed by Excel formulas (VLOOKUP, XLOOKUP, INDEX/MATCH)
• Small-to-medium datasets (tens of thousands of rows rather than millions)
• Audit/validation layers where you want someone to be able to spot-check the raw data coming through a specific stage
• Output feeding pivot tables that don't need the Data Model's relationship capabilities

The hidden cost of worksheet tables

Every row in a worksheet table is stored twice: once in the Power Query cache and once in the Excel grid. For a 100,000-row dataset, this adds up fast. It also means that every time the workbook calculates, Excel has to maintain those cells in memory.

More critically, worksheet tables are editable. A user who doesn't know the table is query-managed might delete rows, change values, or sort the data — and then the next refresh overwrites their changes. This creates support headaches that are entirely avoidable.

Changing load destination for an existing query

In Excel's Queries & Connections pane (Data > Queries & Connections), right-click any query and choose Load To.... The dialog that appears gives you three radio buttons: Table, Only Create Connection, and Data Model. You can also access this from the Power Query Editor via Home > Close & Load To.

The key thing to understand here: changing a query's load destination doesn't change the query's M code at all. The transformations stay identical. You're only changing the output contract — where the engine delivers results after it finishes evaluating.

---

Connection-Only Queries: The Backbone of Staging Architecture

Connection-only is the most misunderstood load destination, and it's also the most powerful for building real ETL pipelines. When a query is connection-only, it participates in the query graph as a node that other queries can reference, but it doesn't write its output to the workbook.

Think of connection-only queries as the staging and intermediate layers of your pipeline. In data warehousing terms, they're your raw and cleansed layers that feed into your conformed or presentation layer.

A realistic example: Sales reporting pipeline

Imagine you're building a monthly sales report in Excel. Your data comes from three sources:

• A SQL Server database with transaction records
• A SharePoint list with territory assignments
• A manually maintained Excel file with product categories

A naive approach loads all three sources directly to worksheet tables and then builds a fourth query that merges them. The result: three unnecessary worksheet tables cluttering the workbook, three times the memory overhead, and three tables users can accidentally corrupt.

The right approach:

1. raw_transactions — Connects to SQL Server. Connection-only.
2. raw_territories — Connects to SharePoint. Connection-only.
3. raw_categories — Connects to the Excel file. Connection-only.
4. clean_transactions — References raw_transactions, applies type corrections and null handling. Connection-only.
5. dim_territory — References raw_territories, deduplicates and standardizes. Connection-only.
6. fact_sales — Merges clean_transactions, dim_territory, and raw_categories. Loads to Data Model.

Six queries, one output. Five of them are invisible to workbook users. The only thing that lands in the Data Model is the fully shaped, final result.

Setting connection-only via Close & Load To

In the Power Query Editor:

1. Navigate to the query you want to make connection-only.
2. On the Home tab, click the dropdown arrow under Close & Load, then choose Close & Load To....
3. In the Import Data dialog, select Only Create Connection.
4. Click OK.

From the Queries & Connections pane without opening the editor:

1. Right-click the query.
2. Choose Load To....
3. Select Only Create Connection.

The query's icon in the pane will update to show no worksheet or data model indicator — just a small connection symbol. When you refresh, the query runs but produces no visible output.

Connection-only doesn't mean free

Because connection-only queries still execute during refresh, you need to think carefully about which ones are inherently expensive. If raw_transactions pulls 5 million rows from SQL Server, that query runs every time anything in its dependency chain refreshes. This is where query folding becomes critical — if your staging transformations fold back to the SQL engine, you're not dragging 5 million rows through the M engine unnecessarily.

---

Loading to the Data Model: Power Pivot's Territory

The Data Model is Power Pivot's columnar in-memory store. When you load a query to the Data Model, it becomes a table available for DAX measures, relationships, and Power BI-style visualizations in Excel. This is where you want your final, authoritative, fully-shaped data to land.

Why the Data Model beats worksheet tables for large data

The VertiPaq engine compresses columnar data extremely efficiently — often achieving 10:1 or better compression on typical business data. A dataset that takes 50MB as a worksheet table might take 5MB in the Data Model. For millions of rows, this difference is the margin between a usable file and one that crashes on open.

The Data Model also enables relationships between tables, which means you can load your fact table and your dimension tables separately and let DAX handle the joins at query time. This is fundamentally more powerful than pre-joining everything in Power Query — though there are scenarios where pre-joining in M is the right call.

Configuring load to Data Model

In the Import Data dialog (Close & Load To or Load To from the pane):

1. Select Table or Only Create Connection.
2. If you want Data Model, check Add this data to the Data Model at the bottom of the dialog.

Note the subtlety: the Data Model checkbox is separate from the destination radio button. You can:

• Load to a worksheet table AND add to the Data Model (two copies of the data — almost never what you want)
• Load connection-only AND add to the Data Model (the canonical pattern for large datasets — no worksheet table, but data available for DAX)
• Load to worksheet table only, not in Data Model

When to use worksheet table vs Data Model

Scenario	Recommendation
Data feeds Excel formulas (VLOOKUP, XLOOKUP)	Worksheet table
Data feeds PivotTables with DAX measures	Data Model
Data feeds Power BI after publishing	Data Model
Dataset is under 50K rows, no DAX needed	Worksheet table
Dataset is 100K+ rows	Data Model
Multiple tables need relationships	Data Model
Output must be visible/auditable by users	Worksheet table

---

Controlling Refresh Behavior Across Multiple Queries

Load destination is one half of the control story. The other half is refresh behavior — specifically, which queries refresh when, and whether they can be refreshed independently.

Selective refresh in Excel

In Excel, you can right-click any individual query in the Queries & Connections pane and choose Refresh to refresh only that query. This is useful during development when you want to test one stage without triggering expensive upstream queries.

However, this can create inconsistency in production: if you refresh fact_sales without refreshing raw_transactions, your output reflects stale source data. For automated scenarios (where refresh is triggered by a scheduled task or VBA), you typically want to refresh all queries at once via Data > Refresh All.

Controlling refresh in Power BI Desktop

Power BI Desktop doesn't have the same selective refresh capability at the query level that Excel does. All queries refresh together when you trigger a refresh, either manually or when the report is published to the Power BI service and a scheduled refresh runs.

This makes connection-only query architecture even more important in Power BI — because you can't easily skip expensive staging queries, they need to be fast. This is where parameterized queries and incremental refresh patterns become essential for managing large datasets.

Disable load vs. connection-only

There's a subtle but important distinction in Power BI Desktop that doesn't exist the same way in Excel: "Enable load" is a toggle on each query. When you uncheck Enable Load on a query in Power BI, it becomes the equivalent of connection-only — it can be referenced by other queries but doesn't produce a table in the report.

To toggle this in Power BI Desktop:

1. Right-click the query in the Queries panel.
2. Uncheck Enable Load.

The query name will appear italicized in the panel, indicating it's not loading to the model. This is how you build staging and intermediate transformation layers in Power BI — identical in concept to connection-only in Excel, different in terminology.

---

Building a Multi-Stage Architecture: A Real-World Project

Let's build this properly. You're creating a regional sales performance dashboard in Excel. Data comes from:

• A folder of CSV files (one per month, one per region) from the finance team
• An Excel workbook maintained by HR with sales rep names and regions
• A manually curated product hierarchy table you've built in Power Query using pure M

The goal: one Data Model table — fact_sales_enriched — that joins transactions with rep names and product hierarchy, with no intermediate worksheet tables cluttering the workbook.

Step 1: Build the raw source queries as connection-only

raw_csv_sales — Uses folder combination to stack all CSV files. Connection-only.

This query uses the standard folder combine pattern. After combining, you have all transactions with a Source.Name column identifying the file origin. No transformations yet.

raw_hr_reps — Connects to the HR Excel workbook's named range. Connection-only.

dim_product_hierarchy — A hardcoded or M-generated lookup table. Connection-only.

Step 2: Build cleaned intermediate queries as connection-only

clean_sales — References raw_csv_sales. Applies type conversions, removes nulls in key columns, standardizes region codes.

let
    Source = raw_csv_sales,
    TypedColumns = Table.TransformColumnTypes(Source, {
        {"TransactionDate", type date},
        {"Amount", type number},
        {"RegionCode", type text},
        {"ProductID", type text},
        {"RepID", type text}
    }),
    RemoveNullAmounts = Table.SelectRows(TypedColumns, 
        each [Amount] <> null and [Amount] > 0),
    StandardizeRegion = Table.TransformColumns(RemoveNullAmounts, {
        {"RegionCode", Text.Upper, type text}
    })
in
    StandardizeRegion

Set this as connection-only.

clean_reps — References raw_hr_reps. Selects only the columns you need (RepID, FullName, Region), deduplicates on RepID.

let
    Source = raw_hr_reps,
    SelectColumns = Table.SelectColumns(Source, {"RepID", "FullName", "Region"}),
    Deduplicated = Table.Distinct(SelectColumns, {"RepID"})
in
    Deduplicated

Set this as connection-only.

Step 3: Build the final output query, load to Data Model

fact_sales_enriched — Merges everything together. This is the only query that loads to the Data Model.

let
    Source = clean_sales,
    
    // Join rep names
    MergeReps = Table.NestedJoin(
        Source, {"RepID"},
        clean_reps, {"RepID"},
        "RepData", JoinKind.LeftOuter
    ),
    ExpandReps = Table.ExpandTableColumn(
        MergeReps, "RepData",
        {"FullName", "Region"},
        {"RepFullName", "RepRegion"}
    ),
    
    // Join product hierarchy
    MergeProducts = Table.NestedJoin(
        ExpandReps, {"ProductID"},
        dim_product_hierarchy, {"ProductID"},
        "ProductData", JoinKind.LeftOuter
    ),
    ExpandProducts = Table.ExpandTableColumn(
        MergeProducts, "ProductData",
        {"ProductName", "Category", "Subcategory"},
        {"ProductName", "Category", "Subcategory"}
    ),
    
    // Final column selection and ordering
    FinalColumns = Table.SelectColumns(ExpandProducts, {
        "TransactionDate", "RepID", "RepFullName", "RepRegion",
        "ProductID", "ProductName", "Category", "Subcategory",
        "Amount", "RegionCode"
    })
in
    FinalColumns

When you close and load this query, choose Only Create Connection and check Add this data to the Data Model.

The result

Your Queries & Connections pane shows six queries:

• raw_csv_sales — connection only
• raw_hr_reps — connection only
• dim_product_hierarchy — connection only
• clean_sales — connection only
• clean_reps — connection only
• fact_sales_enriched — loads to Data Model

The workbook has zero query-generated worksheet tables. Users see only the pivot tables and charts you've built on top of the Data Model. The architecture is clean, documented by naming convention, and trivially easy to extend.

---

Handling Refresh Order and Dependencies

Power Query's engine determines evaluation order based on the dependency graph — if fact_sales_enriched references clean_sales and clean_reps, those will always be evaluated first. You don't need to manually control evaluation order within a dependency chain.

What does require attention is when you have multiple independent chains that need to coordinate. For example, if you have both a fact_sales_enriched table and a separate dim_date table both loading to the Data Model, they're evaluated independently — potentially in parallel. This is usually fine, but can cause issues if both chains reference a common volatile source (like an API that rate-limits requests).

Orchestrating multi-query refresh dependencies goes deep on strategies for managing this in complex pipelines. For most single-workbook scenarios, the dependency graph handles ordering automatically and correctly.

---

Hands-On Exercise

Build the following pipeline from scratch in a new Excel workbook. Use data you create manually or adapt from your own work environment.

Scenario: You have three small tables of order data:

Create three Excel tables in a new workbook:

• Orders: OrderID, CustomerID, ProductID, OrderDate, Quantity, UnitPrice
• Customers: CustomerID, CustomerName, City, Country
• Products: ProductID, ProductName, Category

Populate each with at least 10 realistic rows.

Your tasks:

1. Connect to all three tables using Power Query. Set all three queries as connection-only. Name them raw_orders, raw_customers, raw_products.

2. Create a clean_orders query that references raw_orders, adds a calculated TotalAmount column (Quantity × UnitPrice), and filters out any rows where Quantity is zero or null. Set as connection-only.

3. Create a fact_orders_enriched query that merges clean_orders with raw_customers (on CustomerID) and raw_products (on ProductID). Expand only the columns you need from each dimension. Set this query to load to the Data Model only (connection-only + Add to Data Model).

4. Build a PivotTable in Excel that uses the Data Model — you should see fact_orders_enriched as a table to pivot against.

5. Open the Queries & Connections pane and verify: you should see five queries, four of them connection-only, and the workbook should have zero query-generated worksheet tables visible.

Stretch goal: Add a sixth query, audit_order_count, that loads to a worksheet table and contains a single row with two columns: RefreshTimestamp and OrderCount. Use DateTime.LocalNow() for the timestamp and Table.RowCount(fact_orders_enriched) for the count. This gives you an audit table that's intentionally surfaced while keeping the main data in the model.

---

Common Mistakes & Troubleshooting

"My connection-only query doesn't seem to do anything"

This is the number one confusion. Connection-only queries produce no visible output — that's the point. If you're expecting to see data somewhere and don't, check whether your final output query (the one that should load to a table or model) is actually configured correctly. Right-click it in the Queries & Connections pane and verify its load destination.

"Refresh All is updating a worksheet I didn't expect"

You have a query somewhere that's still set to load to a worksheet table when you intended it to be connection-only. Check every query in the pane — queries with a worksheet icon next to them are loading to the grid. Also check for any legacy external data connections from before the Power Query era.

"The Data Model table is there but I can't see data in my PivotTable"

The relationship between your Data Model table and any dimension tables needs to exist before DAX aggregations work correctly. If you're loading a single wide table (fact_orders_enriched in the exercise above) with all dimensions already joined in, you don't need relationships — all columns are in the same table. But if you're loading a normalized fact+dimensions structure, you need to define those relationships in the Power Pivot Data Model view.

"Changing load destination broke a downstream query"

Changing a query's load destination never affects the M code or the query's availability as a reference for other queries. Connection-only queries are fully referenceable. What can break downstream queries is if you accidentally delete a query while trying to change its destination, or if you rename it. Always use the right-click Load To approach, not delete-and-recreate.

"My file got huge after loading to the Data Model"

This usually means you've accidentally checked both "Table" (worksheet) and "Add this data to the Data Model," storing the data twice. Open the Queries & Connections pane, right-click the culprit query, choose Load To, and uncheck whichever destination you don't need. Then save and confirm the file size drops.

"Power BI doesn't have a 'connection-only' option like Excel does"

In Power BI Desktop, the equivalent is unchecking Enable Load on the query. Right-click the query in the Queries panel and look for that toggle. Queries with Enable Load unchecked appear in italics in the panel and don't produce tables in the report model — identical behavior to connection-only in Excel, just different UI terminology.

---

Summary & Next Steps

The ability to control load destinations precisely is what separates an ad-hoc Power Query user from someone who builds production-grade reporting pipelines. Here's the core of what you've learned:

• Worksheet tables are for final output that users or Excel formulas need to interact with directly. Use them intentionally, not by default.
• Connection-only is your staging layer. Every intermediate transformation query should be connection-only — it keeps the workbook clean, prevents user tampering, and communicates the query's role clearly.
• Data Model is where large datasets, DAX-enabled analysis, and relationship-based reporting belong. Loading connection-only plus Add to Data Model is the canonical pattern for production dashboards.
• Load destinations don't affect M code. Changing where output goes is a configuration decision, not a transformation decision. Your query logic stays identical.
• A well-structured pipeline has a clear flow: raw source queries (connection-only) → cleaned/transformed intermediate queries (connection-only) → final output queries (worksheet table or Data Model).

For your next steps, consider how this architecture interacts with more advanced topics:

• Parameterized queries let you make your raw source queries dynamic — passing date ranges or filter values that change what data flows through the entire pipeline. Parameterized Queries and Dynamic Data Sources in Power Query walks through this end to end.

• Multi-stage staging architectures take the pattern you've built here further, creating explicitly separated raw, cleansed, and conformed layers with well-defined contracts between them. Building Multi-Stage Staging Architectures in Power Query is the natural next lesson.

• Scheduling and managing refresh failures becomes critical once your pipeline runs automatically in the Power BI Service. Scheduling and Managing Power Query Refresh Failures in Power BI Service covers diagnostics and recovery workflows.

The architecture decisions you make around load destinations echo through every layer of your reporting solution. Build them deliberately from the start, and you'll save yourself weeks of refactoring later.