How to Add an Existing Power Query to the Excel Data Model
Question details
The user needs to add a previously created, connection-only Power Query into the Excel Data Model.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Modifying the load destination of an existing Power Query to include it in the Data Model for advanced analysis.
- Observed behavior
- The query is currently loaded as connection-only, and the user is unsure how to access the 'Close & Load To' options again to add the data to the Data Model.
Ensure that the Queries & Connections pane is enabled in your workbook so you can view all existing queries.
Change Query Load Settings via Queries & Connections
Use the Queries & Connections pane to modify the load destination of an existing query and add it directly to the Data Model.
Even if you initially saved a Power Query as 'Connection Only,' Excel allows you to modify the load destination at any time without having to recreate the query from scratch.
Navigate to the 'Data' tab on the Excel ribbon and click on 'Queries & Connections' to open the side pane.
In the pane, right-click the specific query you want to modify. From the context menu, select 'Load To...'.
In the Import Data dialog box, check the box that says 'Add this data to the Data Model' at the bottom.
Choose whether you want to keep it as 'Only Create Connection' or load it to a 'PivotTable Report', then click 'OK' to apply the changes.
Try WPS Office for Your Spreadsheet Needs
If you are looking for a visually familiar, lightweight, and highly compatible alternative to Microsoft Office, WPS Spreadsheet is an excellent choice. It handles complex datasets smoothly and offers a rich set of data analysis tools.
- 1. Download WPS Office: Visit the official WPS Office website and click the free download button.
- 2. Install the Software: Run the downloaded installer and follow the quick setup instructions.
- 3. Open Your Excel Files: Launch WPS Spreadsheet and seamlessly open any existing Excel workbooks to manage your data.
Frequently Asked Questions
Why is the 'Add this data to the Data Model' checkbox greyed out?
This usually happens if the query is already loaded to an Excel Table in a worksheet. To fix this, right-click the query, select 'Load To', change the selection to 'Only Create Connection', and then re-open the 'Load To' menu to check the Data Model box.
Can I load a query to both a worksheet table and the Data Model?
Yes. In the 'Load To' dialog box, you can select 'Table' to display the data in a standard worksheet while simultaneously checking the 'Add this data to the Data Model' box.
What is the benefit of adding queries to the Data Model?
Adding data to the Data Model allows you to bypass Excel's 1-million row limit, compress large datasets efficiently, and create relational links between multiple tables for advanced Power Pivot reporting.
