Power Query Problems

How to Update Source and User Input Data in Power Query

WPS Editor Oct 9, 2026 868 views

Question details

The user wants to combine raw source data and manual user reconciliation inputs without creating self-referencing feedback loops or duplicate columns upon refreshing.

How to Update Source and User Input Data in Power Query

Product: Excel
Device & OS: not provided
Scenario: Maintaining raw data securely in one workbook while allowing users to enter reconciliation information in a separate workbook, then combining both sets of data.
Observed behavior: Merging queries creates duplicate columns, writing back creates a self-referencing feedback loop, and Power Query fails to retrieve data from the password-protected source workbook.

Before you start

Before beginning, you must remove password protection from your source workbook. Power Query cannot natively authenticate to or extract data from encrypted or password-protected Excel files.

Solution 1Recommended

Combine Separate Input and Source Tables Using a Unique ID

Since Power Query cannot write data back to a source table, you must maintain strictly separate tables for raw data and user inputs, combining them via a stable Transaction ID.

Power Query is an ETL (Extract, Transform, Load) tool designed to retrieve and shape data, not to write it back. Attempting to update a source table with a query that also reads from it will result in a self-referencing feedback loop.

To solve this, use two separate workbooks: one for raw data and one for user input. Merge them in a third output query using a unique, stable ID.

Create the Source Query

Go to Data > Get Data > From File > From Workbook and select your unprotected raw data file. Load it as a connection only.

Create the User Input Query

Import your user input workbook (which must contain the same unique Transaction IDs as the source) using the same Get Data method. Load it as a connection only.

Merge the Queries

Navigate to Data > Get Data > Combine Queries > Merge. Select your Source Query as the primary table and your User Input Query as the secondary table. Highlight the Transaction ID column in both and select a Left Outer Join.

Expand and Clean Columns

In the Power Query Editor, click the expand icon on the merged table column. Uncheck any columns that already exist in your source data to avoid duplicates, and uncheck 'Use original column name as prefix'. Click OK and load the final table.

Combine Separate Input and Source Tables Using a Unique ID

Transaction ID Stability: Ensure the unique identifier (Transaction ID) never changes in either workbook, as this is the only link between the raw data and the manual user inputs.

Solution 2

Use VBA or Office Scripts for True Data Write-Back

If your workflow absolutely requires user-entered values to be written directly back into the original source table, you must bypass Power Query and use a scripting solution.

Free Microsoft Office alternative

Try WPS Office for Simplified Data Management

Complex Power Query limitations, such as the inability to read password-protected files or write data back, can disrupt your workflow. If you are looking for a straightforward, lightweight, and free alternative to Microsoft Excel for daily data reconciliation and analysis, WPS Spreadsheets is an excellent choice.

1. Download the Installer: Visit the official WPS Office website and click the free download button.
2. Install the Software: Run the downloaded setup file and follow the on-screen instructions to install WPS Office on your computer.
3. Open Your Spreadsheets: Launch WPS Spreadsheets and seamlessly open your existing Excel workbooks to manage your data.

Free and lightweight alternative to Microsoft ExcelFully compatible with Microsoft Excel formats including .xlsx, .xls, and .csvFamiliar user interface requiring zero learning curve for Excel usersBuilt-in advanced tools for data sorting, filtering, and pivot tables

Frequently Asked Questions

Can Power Query write data back to the original source file?

No. Power Query is strictly an Extract, Transform, and Load (ETL) tool meant to retrieve and shape data. It does not have a built-in method to push or write manual user updates back into the source table. You must use VBA, Office Scripts, or Power Apps for that functionality.

Why does Power Query fail to load my password-protected workbook?

Power Query cannot authenticate to or extract data from encrypted or password-protected Excel files. To use the workbook as a data source in Power Query, you must first remove the password protection.

How do I prevent duplicate columns when merging queries in Excel?

After merging queries, click the expand icon on the new table column in the Power Query Editor. Manually uncheck any columns that are already present in your primary table, and ensure 'Use original column name as prefix' is unchecked to keep your column headers clean.

Excel & Data Problems