How to Improve Slow Power Query Refreshes from Excel Workbooks in WPS Spreadsheets
Question details
The user needs to reduce the refresh time of a Power Query that combines multiple macro-enabled Excel workbooks from a folder and feeds a PivotTable.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is combining multiple macro-enabled Excel workbooks from a folder in Power Query to create a PivotTable and needs the refresh to finish faster.
- Observed behavior
- The Power Query refresh takes several minutes even though each source workbook is approximately 1 MB.
Before editing the query, note how many workbooks are in the folder, whether the source data is in worksheets or tables, and the number of sheets, rows, and columns being imported. This helps distinguish storage-related slowdowns from data-volume and transformation-related slowdowns.
Audit and Reduce Data Volume and Transformation Steps in Power Query
Use this first when a folder-based Power Query refresh is slow, because extra rows, columns, files, and transformation steps usually have the largest performance impact.
Excel workbook file size is not a reliable indicator of Power Query performance because Excel files are compressed. A 1 MB workbook can still contain many sheets, rows, columns, formulas, or metadata that Power Query must read and process.
In WPS Spreadsheets, open the workbook that contains the Power Query, click the Data tab, and open Queries & Connections. Right-click the relevant query and choose Edit to launch Power Query Editor.
In Power Query Editor, open the Query Settings pane and review the Applied Steps list. Look for steps that expand many columns, merge with large tables, duplicate data, or process columns that the PivotTable does not need.
Select the Choose Columns or Remove Columns option, keep only the fields required for the PivotTable, and apply the change as early as possible in the query steps.
Use the filter drop-down on source columns to remove blank rows, irrelevant categories, or date ranges before performing merges, grouping, or expansion.
Apply the changes, close and load the query, then refresh the PivotTable and compare the new refresh time with the previous baseline.
Optimize Source File Location and Consolidate Workbook Sources
Use this when many workbooks are stored on a network path or when the folder contains files that are not needed for the PivotTable.
Convert Source Ranges to Excel Tables and Reduce Workbook Count
Use when sources are unstructured worksheet ranges or the folder contains many small files.
Speed Up Power Query Refresh in WPS Spreadsheets
WPS Spreadsheets lets you open and edit Power Query queries, remove unneeded columns and rows, adjust source paths, and refresh PivotTables to reduce folder-based refresh time.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the workbook that contains the Power Query and PivotTable.
- 2. Open Power Query Editor: Click the Data tab, open Queries & Connections, right-click the query, and choose Edit.
- 3. Trim query steps and source data: Remove unneeded columns, filter rows, and switch sources from whole sheets to tables where possible.
- 4. Update the source folder path if needed: Open Data Source Settings and point the query to a local folder containing only the required workbooks.
- 5. Refresh and verify: Close and load the query, refresh the PivotTable, and check the improved refresh time.
Frequently Asked Questions
Does workbook file size determine Power Query refresh speed?
No. Excel workbooks are compressed, so a 1 MB file can still contain many rows, columns, sheets, formulas, or metadata. Refresh time depends more on the number of workbooks, storage location, source type, and transformation steps.
Why is my folder-based Power Query slow even though each workbook is small?
The query may be importing entire sheets, combining too many files, reading from a slow network location, or keeping columns and transformation steps that are not needed by the PivotTable. Open Power Query Editor and review the Applied Steps and source settings.
Can removing columns improve Power Query refresh time in WPS Spreadsheets?
Yes. In Power Query Editor, use Choose Columns or Remove Columns to keep only the fields required for the PivotTable. Removing unneeded columns early in the query can significantly reduce memory use and processing time.
Should I combine multiple Excel workbooks into one master workbook?
It can help when many small files cause overhead. Before doing it, check whether the query imports tables or sheets, remove obsolete files, and test the refresh time. A single clean workbook or fewer source files often refreshes faster.
How do I check the current Power Query source path in WPS Spreadsheets?
Open the Data tab and Queries & Connections, right-click the query, and choose Edit. In Power Query Editor, review the source step or open Data Source Settings to see and update the folder path.
