Excel: Replace Monthly Manual Pasting with Power Query
Learn to build a Power Query workflow that combines recurring software exports, cleans data, handles column changes, and refreshes monthly Excel reports without manually copying and pasting files.
At the start of each month, many businesses export data from their software, open an Excel file, delete unnecessary rows, and paste the data into a consolidated report. This approach is not difficult for a one-off task, but errors are more likely when multiple files must be combined or the same steps repeated. Power Query is well suited to this work: it records the processing steps so that next time, staff only need to update the source file and refresh the data. The preparation required does not involve complex formulas, but rather organizing files and establishing column conventions from the outset.
When to switch to Power Query
Switch when a report has recurring inputs and relatively stable cleaning steps. For example, the software may export a receivables file with the same structure each month, or each branch may submit a sales file that needs to be combined into a single table. Power Query is also useful when total rows must be filtered out, blank rows removed, date formats converted, and customer codes standardized before the data is used in a PivotTable.
- A good fit: regularly exported files, multiple files using the same template, repeated cleaning steps, and reports that require source traceability.
- Not needed yet: small tables processed only once, data layouts that change constantly, or reporting requirements that have not yet been agreed.
- Address these issues first: files with merged cells, multi-row headers, or multiple tables stacked within one worksheet. Power Query can read these files, but the processing steps will be difficult to maintain.
Do not start with the largest report. Choose a table with a clear source, an owner who understands how to reconcile it, and output that is used regularly.
Four steps to build a query from software export files
Keep several files from previous periods for testing, including one with blank rows or unusual data. Do not test directly on the report file currently used for submission.
- Separate the source file area. Create a dedicated folder for export files, for example by report type. Place only the files to be combined in this folder; do not keep drafts, temporary files, or output reports in the same location. If there is only one file per period, connect directly to the file instead of using a folder.
- Connect and select the correct data object. In Excel, go to Data > Get Data > From File, then select From Folder when combining multiple files, or From Workbook for a single file. For Excel files, select the table or worksheet containing the detailed data, not the sheet with the report title and summary rows.
- Clean the data in the sample query. Use the header row as column names, remove blank rows and grand total rows, and filter out records outside the report’s scope. Set data types for dates, numbers, and text; check the date format against the source file. When combining files from a folder, perform these steps in Transform Sample File so Excel applies the same processing to each file.
- Load the results and save the refresh setup. Select Close & Load To. Load the data as a table if the report needs to be viewed and adjusted on a worksheet; choose to create a connection only if the data is used exclusively for a PivotTable or data model. In subsequent months, add the new file to the correct folder and select Refresh All, rather than opening each file to copy its contents.
Name queries according to their content, such as “Receivables_Detail”, instead of keeping the default names. Steps in the Applied Steps pane should also be renamed when their purpose is not immediately clear, especially filtering and column renaming steps.
Keeping queries working when column names change
A common error occurs when the software changes “Cust. ID” to “Customer ID”, while later steps still look for the old name. The Changed Type step automatically generated by Excel can also contain a long list of column names, so even a minor change can cause the query to fail. To reduce this dependency, standardize names immediately after reading the data, then use the standardized names throughout all subsequent processing.
- Create a mapping table with two columns: Name in file and Standard name. For example, both “Cust. ID” and “Customer ID” map to “CustomerID”; each file should contain only one source name corresponding to each standard name.
- Import the mapping table into Power Query and use it for the renaming step. Users comfortable editing M can use Table.RenameColumns with MissingField.Ignore to ignore old names that do not appear in the current file.
- Set data types and write calculations only after the renaming step. Do not reference columns by position, such as “the third column”, because the column order in export files can change.
- Create a list of required columns and check it with Table.ColumnNames before processing. If any columns are missing, the query should clearly identify them by name instead of silently producing a table with missing data.
When a file uses new column names, test the refresh on a copy first. If only additional mappings are needed, update the column name table; if the meaning of the data has changed, review the report logic rather than simply renaming columns so the query runs.
Checks after every refresh
Power Query automatically repeats the processing steps, but it does not verify that the results are correct from a business perspective. After Refresh All, reconcile the results with the source files before releasing the report.
- Check the row count after filtering and explain the excluded rows.
- Reconcile selected summary figures, such as total activity or the closing balance, against the original report.
- Open the error messages for each query; do not use the previous results table if the refresh has not completed successfully.
- Retain source files by period so figures can be traced if questions arise later.
Actions to take now
- Select a report that is regularly consolidated by manual pasting and save sample source files.
- Build the query following the four steps, then reconcile the results with the current report.
- Create a standardized column name table and document the checks required after each refresh.
A query that is easy to maintain matters more than one with many clever processing steps. When data flows between export files and management reports need to be reviewed, VASCOM can work with businesses to standardize inputs before automation.