How to Preserve Manual User Inputs During Power Query Refresh in Excel
Question details
The user needs a reliable method to prevent manually entered data fields from being overwritten or lost when an Excel Power Query table is refreshed.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing an employee dataset where dynamic data loaded via Power Query must coexist with manual user input columns in the same view.
- Observed behavior
- When the Power Query output table is refreshed, any manually typed values in adjacent columns are overwritten, deleted, or become misaligned with the source rows.
Before modifying your queries, ensure your primary dataset has a distinct unique identifier column (such as an Employee ID) to accurately link your manual inputs to the source records.
Use a Separate Manual Input Table and Merge Queries
This is the safest and most reliable method. It keeps the automated source data and your manual entries in separate tables, combining them safely within Power Query.
Entering manual values directly into a Power Query output table often leads to data loss because a refresh completely rebuilds the table. By maintaining two separate tables and merging them, you ensure your manual inputs are safely mapped to the correct row regardless of sorting or new data entries.
In your Excel workbook, create a new table containing the unique key (e.g., Employee ID) and the columns required for your manual data entry. Press Ctrl+T to format it as a Table.
Select your source employee data table, go to the 'Data' tab, and click 'From Table/Range'. Select 'Close & Load To...' and choose 'Only Create Connection'. Repeat this exact step for your new manual input table.
Open the Power Query Editor. Select your main employee query, click 'Merge Queries' on the Home ribbon, and select the manual input table from the dropdown. Click the unique key column in both table previews to link them, then click 'OK'.
In the merged query, locate the new column added at the end. Click the two-arrow expand icon in the column header, select the manual input fields you want to display, and uncheck 'Use original column name as prefix'.
Click 'Close & Load' to output the combined table into a new worksheet. You can now update your manual inputs in the separate table, and clicking 'Refresh All' will perfectly align them in the final output.
Create a Self-Referencing Table in Power Query
This advanced technique allows users to type directly into the Power Query output table by feeding the output back into the query as historical data.
Manage Spreadsheets Seamlessly with WPS Office
If you find complex data merging and Power Query setups in Microsoft Excel frustrating, consider switching to WPS Office. It provides an intuitive, lightweight spreadsheet application that natively handles complex datasets and supports all standard Microsoft Office file formats.
- 1. Download and Install: Visit the official WPS website to download the free, lightweight WPS Office suite.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx files. All your data and formatting will remain perfectly intact.
- 3. Analyze Data Easily: Utilize the built-in intuitive data tools to sort, filter, and manage your datasets without complex background queries.
Frequently Asked Questions
Why do my manual entries disappear when I refresh Power Query?
When an Excel Power Query table refreshes, it completely deletes the existing output table and reconstructs it from the source data. Any manual columns or data added directly to that output sheet are erased unless they are linked using a merged query.
Do I need a unique ID column for merging tables in Power Query?
Yes, a unique identifier (like an Employee ID, SKU, or Transaction Number) is mandatory. It acts as the exact match criteria so your manual data always aligns with the correct row, even if the source data changes order.
Can I use VLOOKUP instead of Power Query to keep manual inputs?
Yes. You can keep your manual inputs in a master Excel table and use VLOOKUP or XLOOKUP to automatically pull the dynamic data from the Power Query output table into your master table. However, this approach can become slow if your dataset contains tens of thousands of rows.
