How to Append Daily Power Query Results in WPS Spreadsheets Without Overwriting Historical Data
Question details
The user needs to append daily Power Query output to a historical table in WPS Office without overwriting previous results.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A daily Excel source file with the same filename is processed, and each day's date, location, and total must be added to a historical table.
- Observed behavior
- The query replaces existing rows in the destination table instead of appending new daily records.
Before you start, confirm that the daily source workbook is saved in a consistent folder and that the historical table columns—such as Date, Location, and Total—match the columns returned by your Power Query. Have the source path ready so you can point the query to the right file.
Use a Self-Referencing Table to Append Daily Rows in WPS Power Query
Best when you need to keep the same query file open and automatically add a new record each day without replacing the destination table.
A self-referencing query reads the current historical table, combines it with the new daily source rows, and writes the combined result back to the same table. The key is to add a unique key column so duplicate daily files do not repeat previous rows.
In WPS Spreadsheets, select Data > Get Data > From File > From Workbook and choose your daily Excel file. In Power Query Editor, keep the columns for Date, Location, and Total.
Select the Date, Location, and Total columns, then go to Add Column > Merge Columns to create a unique key that identifies each daily record.
In Power Query Editor, create a new query from your historical table by selecting Data > Get Data > From Table/Range after selecting the historical table range.
Use Home > Append Queries in Power Query Editor to combine the historical query with the daily source query, keeping the unique key column.
Choose Close & Load To and set the destination to the historical table location. The query now retains previous records and appends only new daily rows.
Separate Daily Staging and Historical Queries to Add Only Missing Keys
Use this when you want a more controlled append and want to see which rows would be added before loading into the historical table.
Append Daily Power Query Records in WPS Spreadsheets Without Overwriting History
Use WPS Spreadsheets to build a self-referencing Power Query workflow that appends the current day's Date, Location, and Total to an existing table. Your historical data stays intact, and the resulting table can be refreshed or exported to Power BI.
- 1. Create a unique key: In WPS Spreadsheets, open Data > Get Data > From File > From Workbook, select your daily workbook, and in Power Query Editor use Add Column > Merge Columns to combine Date, Location, and Total into a unique key.
- 2. Import your historical table: Select the existing historical table range, then go to Data > Get Data > From Table/Range so Power Query can read all previous records.
- 3. Append the new daily rows: In Power Query Editor, use Home > Append Queries to combine the historical query with the daily query, then use Home > Remove Rows > Remove Duplicates on the unique key column.
- 4. Load back to the historical table: Choose Close & Load To, select the existing historical table destination, and refresh the query after placing the next daily file in the source folder.
Frequently Asked Questions
Why does my daily Power Query result overwrite the historical table?
Because the query is currently loaded to the same destination table without including the existing historical rows. Add the current historical table as a source query, append the new daily rows, and load the combined result back to the table.
How do I create a unique key from Date, Location, and Total?
In Power Query Editor, select the three columns, go to Add Column > Merge Columns, choose a separator, and name the new column. Use Home > Remove Rows > Remove Duplicates on this key so repeated days are not added twice.
Can I still use this historical table in Power BI?
Yes. After loading the appended table in WPS Spreadsheets, open Power BI Desktop and use Get Data > Excel workbook, select the historical table, and create your reports. The table includes every retained daily record.
What should I do if the source file keeps the same filename every day?
Save the daily file to the same folder and keep the same sheet and column names. In Power Query, point to the current file, then refresh the query after replacing the file with the new daily version, so the append workflow reads the latest data.
What if duplicate rows appear after refresh?
Use a unique key column and apply Remove Duplicates in Power Query before loading to the historical table. Check that Date, Location, and Total are combined consistently and that no blank values create mismatched keys.
