How to Combine Monthly Reports from Multiple Stores with Power Query
Question details
The user needs to consolidate monthly reports containing thousands of product IDs from multiple stores into a single worksheet without copying and pasting manually.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Aggregating monthly reports that share similar data structures but might have separate input columns for different stores.
- Observed behavior
- Data is currently scattered across separate input columns or files, requiring an automated consolidation method to unify the product IDs and headers into one master dataset.
Ensure all store report files are stored in a single folder and verify that they share a consistent structure, such as matching column names and data types, to prevent parsing errors.
Consolidate Multiple Store Reports using Power Query
Use the Get Data from Folder feature in Power Query to automatically append and transform multiple worksheets into a single, structured dataset.
Power Query is ideal for appending data from multiple files sharing a similar structure. If the input layout features store names as separate columns, you can also use Power Query's unpivot function to normalize the data into a flat, tabular layout (e.g., Product ID, Store, Month, Value) suitable for PivotTables.
Navigate to the Data tab, select Get Data > From File > From Folder, and browse to the directory containing all your monthly store reports.
Click Combine & Transform Data. In the preview window, select the worksheet containing the data and click OK to open the Power Query Editor.
If stores or months are spread across separate columns, select those columns, right-click the header, and choose 'Unpivot Columns' to convert them into a normalized format with Attribute and Value columns.
Once the data types and headers are standardized, click 'Close & Load To...' and select Table to load the consolidated dataset into a new master worksheet.
Use VLOOKUP for Specific Template Consolidation
If all store reports are on different sheets within the same workbook and follow a strict identical layout, you can use lookup formulas instead of Power Query.
Easily Combine Multiple Worksheets with WPS Spreadsheets
WPS Spreadsheets provides robust, built-in tools like 'Merge Workbooks' and 'Consolidate' that allow you to seamlessly aggregate data from multiple store reports without needing to write complex queries or transformations.
- 1. Open WPS Spreadsheets: Launch WPS Office and open a new blank workbook or your master template.
- 2. Access the Consolidate Tool: Navigate to the Data tab on the ribbon and click on the 'Consolidate' button.
- 3. Add Reference Ranges: In the dialog box, click the 'Add' button to select the specific data ranges from your various store worksheets.
- 4. Merge the Data: Select your desired function (such as Sum or Average), check the boxes to use labels in the top row or left column, and click OK to generate your consolidated report instantly.
Frequently Asked Questions
Why are some columns missing when combining files in Power Query?
This generally happens if the column headers in your store reports do not match exactly. Power Query is case-sensitive, so 'Product ID' and 'product id' will be treated as separate columns. Ensure all files have identical column names.
What does 'Unpivot Columns' do and why should I use it?
'Unpivot Columns' takes wide datasets (where months or individual stores are spread across multiple columns) and transforms them into a flat, tabular format. This provides one row per product, store, and month, which is the required format for generating accurate PivotTables.
Will Power Query update my consolidated sheet automatically when a new store report is added?
Yes. If you used the 'From Folder' import method, you just need to place the new store report into the designated folder, open your Excel workbook, and click 'Refresh All' on the Data tab. The query will run and append the new data automatically.
Can I combine files of different formats, like CSV and XLSX?
Yes, but they cannot be combined instantly using the standard 'Combine Files' button without encountering errors. You must import the CSV and XLSX files as separate queries, apply the necessary transformations to align their columns, and then use the 'Append Queries' function.
