'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Load the Correct Excel Table from Power Query
Question details
The user needs to load a modified query as a table in an Excel worksheet instead of the original source table.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Modifying data in Power Query and attempting to output the final processed result into a worksheet.
- Observed behavior
- Excel loads the original source data table instead of the modified query, often because the modified query is configured as connection-only.
Ensure that you have applied and saved your recent data transformation steps in the Power Query Editor before adjusting the load destinations in your workbook.
Change Query Load Settings via Queries & Connections
By explicitly configuring which query loads to a table and which remains a background connection, you ensure the correct data appears in your worksheet.
When working with Power Query, it is common to have intermediate queries that pull raw data and final queries that contain the cleaned results. By default, Excel might load the raw data to a table while keeping your modified query as a background connection. You can fix this by adjusting the 'Load To' settings.
Locate the incorrectly loaded source table in your Excel worksheet, select it, and delete it. This action will automatically change its query status to 'Connection Only'.
Navigate to the 'Data' tab on the Excel ribbon and click on 'Queries & Connections' to open the side pane.
In the Queries & Connections pane, find the query that contains your modified data. Right-click on it and select 'Load To...' from the context menu.
In the Import Data dialog box, choose 'Table'. Select 'Existing worksheet' and click your desired destination cell, or choose 'New worksheet'. Click 'OK' to confirm.
Check the other queries in the pane. Right-click any query you do not want visible in the worksheet, select 'Load To...', and ensure it is set to 'Only Create Connection'.
Use WPS Office for Lightweight and Efficient Spreadsheet Management
While advanced Power Query features are specific to Microsoft Excel, WPS Office provides a fast, free, and lightweight alternative for everyday data analysis and spreadsheet tasks. It offers full compatibility with Microsoft Office formats, allowing you to view, edit, and manage your data seamlessly without a costly subscription.
- 1. Download and Install: Visit the official WPS Office website to download the free suite and follow the quick installation prompts.
- 2. Open WPS Spreadsheets: Launch WPS Office and select the Spreadsheets module to start working with your data.
- 3. Edit Excel Files Seamlessly: Open your existing .xlsx or .xls files directly in WPS Office to continue editing with full formatting compatibility.
Frequently Asked Questions
What does 'Connection Only' mean in Power Query?
'Connection Only' means that the query connects to your data source and processes the information in the background, but it does not output the final results into a physical table on your Excel worksheet. This is highly useful for intermediate steps or extremely large datasets.
Why did Power Query load my original source data instead of my edited query?
This typically occurs when the edited query's load destination is defaulted or set to 'Connection Only', while the original data query is configured to load as a Table. You must manually switch their load destinations in the Queries & Connections pane.
How do I open the Queries & Connections pane?
You can open it by navigating to the 'Data' tab on the top Excel ribbon and clicking the 'Queries & Connections' button. The pane will appear on the right side of your application window.
Can I change a loaded table back to a connection-only query without losing my work?
Yes. You can simply highlight and delete the physical table from your worksheet. Excel will retain the query in the background, automatically reverting its status to 'Connection Only' without losing any of your applied transformation steps.
