Automate Cost Comparisons Across Multiple Excel Workbooks Using Power Query
Question details
The user needs to calculate and compare total costs against average-price totals across roughly 20 identically structured workbooks without relying on manual What-If scenarios.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing vendor prices and item quantities across multiple shared-structure workbooks to identify cost variations.
- Observed behavior
- The user requires an automated setup to append data from multiple files into a master list and run comparisons, rather than processing each workbook individually.
Ensure all the workbooks you want to combine have identically named column headers and are gathered into a single, dedicated folder on your computer.
Consolidate Workbooks and Calculate Totals Using Power Query
Use the Get Data from Folder feature in Power Query to automatically append all 20 workbooks into a single master table for seamless cost comparison.
Power Query excels at automating repetitive tasks like combining identically structured files. By placing all your files in one folder, you can create a single query that updates automatically whenever new workbooks are added or underlying data changes.
Place all 20 identically structured workbooks into a single, dedicated folder on your local drive or network.
Open a new blank Excel workbook. Go to the Data tab, click Get Data > From File > From Folder, and locate your dedicated folder.
Click Combine & Transform Data in the preview window. Excel will merge all sheets into a single Power Query Editor window.
In the Power Query Editor, go to Add Column > Custom Column. Create formulas to calculate the actual total cost (Quantity * Vendor Price) and perform average-price comparisons.
Click Close & Load on the Home tab to export the consolidated and calculated data into a new worksheet or a PivotTable.
Prepare Sample Data for Advanced Custom M-Code
If your average-price calculations require complex relationships, preparing an anonymized sample is the best way to develop a customized Power Query solution.
Combine Multiple Workbooks Automatically with WPS Spreadsheet
WPS Spreadsheet offers a powerful built-in 'Merge Workbooks' tool that simplifies combining data from multiple files without needing to write complex Power Query scripts. It is a highly efficient way to consolidate your 20 vendor workbooks into one comparative sheet.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new blank spreadsheet.
- 2. Access the Merge tool: Navigate to the Data tab on the top ribbon and click on the Merge Workbooks icon.
- 3. Import your files: Click Add Files in the dialog box and select all the vendor workbooks you wish to consolidate.
- 4. Merge identical sheets: Choose the option to combine identically structured sheets into one master worksheet.
- 5. Run comparisons: Click Start Merging. Once combined, use standard SUM and AVERAGE formulas on the master dataset to compare costs.
Frequently Asked Questions
Does Power Query automatically update when I add a new workbook to the folder?
Yes. As long as the new workbook shares the same column structure and is placed in your designated folder, clicking 'Refresh All' on the Excel Data tab will automatically append the new data to your master table.
How do I calculate the average item price across all workbooks using Power Query?
Inside the Power Query Editor, you can use the 'Group By' feature on the 'Item' column and select 'Average' as the operation for the Price column. You can then merge this aggregated query back into your main dataset to calculate cost variations.
What happens if one of my 20 workbooks has different column names?
Power Query relies on exact matches for column headers. If a workbook has slightly different column names, Power Query will create new columns for those mismatches, resulting in null values. Ensure all headers are strictly identical before appending.
Can I use PivotTables to compare the costs after using Power Query?
Absolutely. When you click 'Close & Load To...', select 'PivotTable Report' instead of a standard Table. This allows you to easily drag your Item fields to rows and compare actual costs vs. average costs using PivotTable calculated fields.
