Combine Monthly CSV Files into an Excel Master Table with Power Query
Question details
Combine monthly CSV files into a master table that can be refreshed as new files are added.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A user has monthly CSV files containing sellers and profit totals and wants one row per seller with a separate total for each month.
- Observed behavior
- The desired table shows each seller's monthly totals in separate columns, with blank cells for months when the seller was absent.
Put the monthly CSV files in one dedicated folder and give them identical column headings, such as Name, Date, and Total. Use a consistent date for each month, such as its first day, and make sure Total contains numeric values.
Combine the CSV files and create a monthly PivotTable
Use a folder query to append the files, then summarize the combined data by seller and month.
In Excel, select Data > Get Data > From File > From Folder. Choose the folder containing the monthly CSV files.
In the folder preview, select Combine & Transform Data. Check the sample file and confirm the CSV settings so Power Query reads the headers and columns correctly.
In Power Query Editor, verify that Name, Date, and Total are present. Set Date to a date type and Total to a numeric type, then select Home > Close & Load to load the combined data into Excel.
Click the loaded table and select Insert > PivotTable. Drag Name to Rows, Date to Columns, and Total to Values; if needed, open Value Field Settings and choose Sum. If Excel groups the dates in an unwanted way, right-click a date heading and select Ungroup.
Add each new monthly CSV to the same folder, then select Data > Refresh All in Excel. Check that the new month appears in the PivotTable; if it does not, right-click the PivotTable and select Refresh after the query finishes.

Review monthly CSV data with WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar interface for opening and working with spreadsheets. Its compatibility with common Excel formats makes it easy to move existing files, though it does not fix Excel Power Query or replace the folder-query workflow described above.

Frequently Asked Questions
Do all monthly CSV files need the same column headings?
Yes. Use consistent headings such as Name, Date, and Total so the folder query can combine corresponding fields reliably.
How do I add a new month's data to the master table?
Save the new CSV in the source folder using the same structure, then select Data > Refresh All in Excel. If needed, refresh the PivotTable again after the query finishes.
Why should I use a Date column instead of a different total column in each file?
A common Total column and a Date column give Power Query a consistent structure to append. You can then place Date in the PivotTable's Columns area to show a separate total for each month.
Will removing duplicate seller names create one row per seller?
No. Removing duplicate names can discard records from other months. Keep the monthly records in the combined data and use Name in the PivotTable's Rows area to summarize them.
Can I pivot the month column directly in Power Query?
Yes. In Power Query Editor, select the Date column, choose Transform > Pivot Column, and use Total as the values column. Check the pivot step when adding months: a list of pivoted values created by the interface may need updating to include new dates.




