How to Prevent Duplicate Rows When Combining Excel Files with Power Query in WPS Office Spreadsheets
Question details
The user needs to combine multiple Excel workbooks from a folder using Power Query in WPS Office without creating duplicate rows, even when the source reports appear unique and the output workbook is stored outside the source folder.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Combining multiple Excel workbooks from a folder with Power Query into a single report.
- Observed behavior
- The combined result contains apparent duplicate rows even though the individual source files appear unique.
Before editing the query, save the output workbook outside the source folder and enable hidden items in File Explorer so you can see temporary ~$ files or previous output copies that Power Query may accidentally read.
Filter the Source Folder in Power Query Editor by Extension, File Name, and Folder Path
Use this first to prevent hidden temporary files, previous output files, and similarly named copies from being combined into the final result.
Power Query reads every file that matches the folder query, including hidden temporary files such as ~$ files and old output copies. Filtering by extension, name, and path before combining the binary content prevents these extra files from creating duplicate-looking rows.
In WPS Office Spreadsheets, open the workbook with the query, go to Data > Queries & Connections, and double-click the combine query to open Power Query Editor.
In the Applied Steps pane, select the step that lists folder files—usually Folder.Files—and click the filter arrow on the Extension column. Keep only .xlsx, .xls, or .xlsm, depending on the source files.
Click the filter arrow on the Name column, choose Text Filters > Does Not Begin With, and enter ~$ to exclude Excel temporary files. Add similar text filters for words such as copy or output if needed.
Click the filter arrow on the Folder Path column and select only the intended source folder. Remove any path that points to the output workbook location or to subfolders containing old reports.
After applying the filters, continue with the existing Combine Files or binary expansion steps, refresh the preview, and confirm the file count now matches the intended source files.
Check That the Combine Step Expands Each Sheet or Range Only Once
Use this when the folder filters are correct but the same rows still repeat, which usually means the query is expanding the same worksheet, table, or range more than once.
Review the Full M Code and Compare the Query File List with the Actual Folder
Use this when duplicate rows remain after filtering and combine-step checks, or when the query still returns more files than the folder should contain.
Prevent Duplicate Rows When Combining Excel Files with Power Query in WPS Office
Use WPS Office Spreadsheets to open Power Query, filter the source folder by file extension, name, and path, and refresh the combined query so only the intended Excel workbooks are merged—without duplicate rows from temporary or previous output files.
- 1. Open the workbook and query: In WPS Office Spreadsheets, open the workbook that contains the combine query and go to Data > Queries & Connections to open Power Query Editor.
- 2. Filter the source files: Select the Folder.Files step, filter the Extension column to .xlsx, .xls, or .xlsm, and filter the Name column to exclude ~$ temporary files.
- 3. Restrict the folder path: Filter the Folder Path column to include only the intended source folder and exclude the output workbook location.
- 4. Refresh and load: Refresh the query preview to confirm the file list matches the actual source folder, then load the query to obtain the combined result without duplicate rows.
Frequently Asked Questions
Why does my combined Excel file have duplicate rows even though each source file is unique?
Power Query may also read hidden temporary files such as ~$ files or previous output copies from the source folder. Inspect the Folder.Files step and filter by extension, file name, and folder path so only the intended source files are combined.
Should I save the output workbook in the same folder as the source files?
No. Keep the output workbook outside the source folder. If it must be inside the folder, add a name or path filter in Power Query to exclude the output file from the query.
How do I exclude temporary Excel files from a folder query in WPS Power Query?
Open Power Query Editor, select the Folder.Files step, and filter the Name column using Text Filters > Does Not Begin With. Enter ~$ to exclude Excel temporary files, and filter the Extension column to only .xlsx, .xls, or .xlsm.
How can I tell which files Power Query is actually combining?
Open Power Query Editor and click Home > Advanced Editor to review the M code. Compare the file list produced by Folder.Files or Folder.Contents with the actual files in File Explorer, including hidden items.
Can I edit Power Query M code in WPS Office Spreadsheets to remove duplicate source files?
Yes. In Power Query Editor, go to Home > Advanced Editor, adjust the Folder.Files path or name filters, click Done, and refresh the query to verify that only the intended source files are loaded.
