Power Query Problems

How to Load Power Query Data to an Excel Worksheet and Data Model

Adam Davis Oct 12, 2026 869 views

Question details

The user wants to display their Power Query results in a standard Excel worksheet while simultaneously loading the data into Power Pivot or the Excel Data Model for advanced analysis.

How to Load Power Query Data to an Excel Worksheet and Data Model

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing and transforming data in Power Query to be used for visible table analysis and underlying complex data modeling.
Observed behavior: The user needs to configure query settings so data populates a worksheet table and the Data Model at the same time, or they need to fix the issue where the Data Model option is disabled.

Before you start

Ensure that your data source is correctly connected and refreshed in Power Query, and verify that the Power Pivot add-in is enabled in Excel to utilize the Data Model features.

Solution 1Recommended

Load a New Query to Both Destinations via Close & Load Options

Use the 'Close & Load To' feature when finishing your data transformation to immediately choose multiple destinations.

When finalizing your query in the Power Query Editor, you can specify exactly where the transformed data should be placed. Selecting multiple destinations ensures your data is visible in the workbook while also being available for Power Pivot relationships.

Open the Load Options

In the Power Query Editor, go to the Home tab. Click the drop-down arrow under the 'Close & Load' button and select 'Close & Load To...' from the menu.

Select Table Format

In the Import Data dialog box that appears, select the 'Table' radio button. Choose whether you want to place the table in an 'Existing worksheet' or a 'New worksheet'.

Check Data Model Box

At the bottom of the dialog box, check the box labeled 'Add this data to the Data Model'. Click OK to execute the load and apply your data to both locations.

Load a New Query to Both Destinations via Close & Load Options

Data Successfully Loaded: Your query will now populate a green formatted table in your selected worksheet, and simultaneously become available in the Power Pivot Manage window.

Solution 2

Update Load Destinations for an Existing Query

If your query is already loaded to a worksheet or created as a connection only, you can easily modify its load settings from the main Excel interface.

Solution 3

Enable Missing Data Model Option in Query Options

If the 'Add this data to the Data Model' checkbox is greyed out or unavailable, you must adjust your global or workbook-specific Query Options.

Free Microsoft Office alternative

Looking for a Lightweight Excel Alternative?

If you find Microsoft Excel's data modeling tools too complex or resource-heavy for your daily tasks, try WPS Office. It provides robust spreadsheet functionalities, high compatibility with standard Excel formats, and a fast, user-friendly interface for handling your data processing tasks seamlessly.

1. Download and Install: Click the download button to get the free, lightweight installation package from the official WPS website.
2. Open WPS Spreadsheet: Launch the application and select the Spreadsheet module to start your data analysis in a clean environment.
3. Import Your Excel File: Drag and drop your existing Excel workbook into WPS Spreadsheet to continue working with your imported data seamlessly.

Highly compatible with Microsoft Excel formats (.xlsx, .xls, .csv) for seamless file migrationLightweight architecture that processes and loads large data files quickly without crashingFree to use with a familiar, easy-to-learn interface matching standard spreadsheet toolsIncludes advanced formulas, standard pivot tables, and built-in data consolidation for deep analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why is the 'Add this data to the Data Model' checkbox greyed out?

This usually happens if the query was previously loaded as a connection only or to a different destination type, and the background data engine locks the setting. To fix this, right-click the query in the Queries & Connections pane, select 'Load To...', and reapply the settings. If it persists, check your Query Options under the Data tab to ensure Data Load settings aren't restricted.

Can I load Power Query data to the Data Model without displaying it on a worksheet?

Yes. When the Import Data dialog box appears via 'Close & Load To', simply select the 'Only Create Connection' radio button instead of 'Table', and keep the 'Add this data to the Data Model' box checked. This keeps your workbook file size much smaller while still allowing you to use Power Pivot.

What is the benefit of loading data to the Data Model?

Loading data directly to the Data Model allows you to build relationships between multiple disparate data tables, process millions of rows of data far exceeding Excel's standard sheet limit, and write advanced DAX formulas in Power Pivot for highly complex calculations.

Excel & Data Problems