Combine Monthly CSV Files with Power Query

To combine monthly CSV files in Excel, put them in one folder and choose Data > Get Data > From File > From Folder. Pick Transform Data rather than combining right away, filter the file list to the exports you want, then combine. Set ID columns such as SKU codes to Text so their leading zeros survive. Keep the Source.Name column, and after every refresh, check each file’s row count and sales total.

The Example

Here’s a made-up setup for a small online shop. Each month’s orders come out of the store as a CSV with the same five columns: order_id, order_date, customer_id, sku_code, and net_sales. One row is one order, and the SKU codes are four characters with leading zeros, such as 0041. The first quarter’s files are:

To follow along, make an empty folder just for this test. Paste each block below into a plain-text editor and save it in that folder under the file name shown, with a .csv extension. Leave the SKU codes as typed: 0041, not 41.

data-orders-2026-01.csv

order_id,order_date,customer_id,sku_code,net_sales
ORD-1001,2026-01-08,CUST-01,0041,750.00
ORD-1002,2026-01-19,CUST-02,0042,600.00
ORD-1003,2026-01-27,CUST-03,0043,500.00

data-orders-2026-02.csv

order_id,order_date,customer_id,sku_code,net_sales
ORD-1004,2026-02-05,CUST-01,0041,900.00
ORD-1005,2026-02-14,CUST-04,0044,720.00
ORD-1006,2026-02-23,CUST-02,0042,480.00

data-orders-2026-03.csv

order_id,order_date,customer_id,sku_code,net_sales
ORD-1007,2026-03-04,CUST-03,0043,850.00
ORD-1008,2026-03-16,CUST-05,0045,950.00
ORD-1009,2026-03-28,CUST-01,0041,650.00

The dates are year-month-day, and the sales are plain amounts in dollars. Now copy the January file in the same folder and rename the copy orders-2026-01-backup.csv, the kind of copy someone saves there by accident. Combine everything in the folder, and you get 12 rows and $8,250, overstating the quarter by $1,850 (28.91%). Do the failure tests later in this article on copies of this folder, and keep the original as your clean baseline.

These steps are for Excel for Windows. Excel for the web can’t connect to a folder on your computer. On a Mac, Power Query’s support for folders differs by version, so check Microsoft’s list of data sources by Excel version before you start.

Folder-combine workflow

1. Select monthly CSV files → exclude backup copies
2. Read file + promote headers → remove premature type conversion
3. Expand rows → set SKU to Text and sales to Currency
4. Retain Source.Name → reconcile every file and the total

Step 1: Connect to the Folder and Filter Before Combining

Choose Data > Get Data > From File > From Folder, browse to the folder, and open it. Excel shows a list of the files with buttons to combine them. Microsoft recommends combining immediately only when you’re certain the folder contains nothing but the files you want. Choose Transform Data instead, which opens the file list in the Power Query Editor.

Filter the list before anything is combined:

1. Select the Extension column and choose Transform > Format > lowercase, so .CSV and .csv look the same. Then filter Extension to equal .csv.
2. Filter the Name column with Text Filters > Begins With data-orders-2026-. That drops the backup file, which doesn’t follow the naming pattern. If copies could follow the pattern, add a Does Not Contain filter for words like backup or copy.

Then select the Combine Files icon in the header of the Content column. In the dialog that opens, pick January’s file in the Sample File drop-down (Microsoft’s online guide calls it the example file) so the model doesn’t depend on the order of the folder list. Leave Skip files with errors unchecked. According to Microsoft’s combine files overview, it removes any file that fails to import from the result, so a broken month would drop out without a message.

Step 2: Set the Column Types in the Main Query

Combining creates a few helper queries, including Transform Sample File, the steps applied to every file. After it promotes the first row to headers, Power Query adds a Changed Type step on its own. Two things can go wrong there.

First, the automatic guess may decide sku_code is a whole number and turn 0041 into 41. Second, a type step lists columns by name, and Table.TransformColumnTypes raises an error when a listed column doesn’t exist. If March renamed net_sales to Net_Sales_USD, a type step inside the helper would fail on March’s file.

So keep the helper to two steps, reading the file and promoting headers, and delete its Changed Type step.

The main query needs the same attention. Combining usually adds a Changed Type step at the end of its Applied Steps list too, and it may already have turned sku_code into a number. Setting sku_code to Text after that step gives you “41”, not “0041”, because the zeros are already gone. Microsoft’s CSV combine guide takes the same route: once the helper changes, the output query’s last step shows an error, so the guide deletes that step and then assigns each column’s type. Open Applied Steps in the main query. If the last step is a Changed Type, delete it so that Expanded Table Column1 is the last step. Then select each column and set its type from the column header: order_date to Date, sku_code to Text, net_sales to Currency, and the ID columns to Text. The new step Power Query writes should start from Expanded Table Column1, like this:

Table.TransformColumnTypes(#"Expanded Table Column1", {{"Source.Name", type text}, {"order_id", type text}, {"order_date", type date}, {"customer_id", type text}, {"sku_code", type text}, {"net_sales", Currency.Type}}, "en-US")

The last argument is the culture, which controls how dates and numbers in the files are read. If you do keep a type step in the helper, the culture can be a record that also tells Power Query to use nulls for a missing column instead of failing: [Culture="en-US", MissingField=MissingField.UseNull].

Before loading, check that sku_code shows 0041, not 41, and that no step in Applied Steps shows an error. Then name the query Combined_Orders and choose Home > Close & Load. The data lands in an Excel table, which should take the query’s name; confirm it on the Table Design tab.

Keep Source.Name. It records which file each row came from, and the audit depends on it.

A renamed header can still cause trouble here. Depending on where the type step sits and the Skip setting, the refresh can stop with an error that the column wasn’t found, March can be left out, or March’s rows can load with empty sales. If a refresh shows an error, don’t assume the table on the sheet is current. The audit below catches the other two.

Step 3: Reconcile Every Refresh (Folder_Audit!A1:H5)

On a tab named Folder_Audit, put these headings in A1:H1: Expected_File_Name, Expected_Row_Count, Expected_Sales_Total, Loaded_Row_Count, Loaded_Numeric_Sales_Count, Loaded_Sales_Total, Sales_Variance, Reconciliation_Status. In A2:A4, type the three file names, and in B and C, enter each file’s row count and sales total from the source system. Put TOTAL_CONTROL in A5.

The formulas use the table’s column names rather than fixed ranges like $A$2:$A$100, so they cover every loaded row however long the table grows. In D2:

=COUNTIF(Combined_Orders[Source.Name], A2)

In E2, which counts only real numbers, so an empty or text sales value isn’t counted:

=SUMPRODUCT(--(Combined_Orders[Source.Name]=A2), --ISNUMBER(Combined_Orders[net_sales])) - COUNTIFS(Combined_Orders[Source.Name], A2, Combined_Orders[net_sales], "<0")

In F2: =ROUND(SUMIFS(Combined_Orders[net_sales], Combined_Orders[Source.Name], A2), 2)

In G2, which checks the expected values you typed before comparing:

=IF(OR(TRIM(A2)="", NOT(ISNUMBER(B2)), NOT(ISNUMBER(C2))), "CHECK INPUTS", IF(OR(B2<=0, B2<>INT(B2), C2<0), "CHECK INPUTS", ROUND(F2 - C2, 2)))

In H2:

=IF(G2="CHECK INPUTS", "CHECK INPUTS", IF(D2=0, "MISSING FILE IN FOLDER", IF(D2<>B2, "ROW COUNT MISMATCH", IF(E2<>D2, "CHECK SALES: EMPTY, TEXT OR NEGATIVE", IF(G2<>0, "SALES VARIANCE: $" & TEXT(ABS(G2),"0.00"), "RECONCILED")))))

Once D2:H2 are in, select D2:H2 and copy them down through D4:H4, so each file row has all five formulas. Row 5 adds up the whole table, and its formulas are in the reference section at the end. With the three correct files, every status says RECONCILED: 9 rows and $6,400. Here’s what each failure looks like:

The empty-sales row shows what the audit reports if March’s sales come in empty. It isn’t a prediction of what a renamed header will do, because that depends on your steps and settings. A refresh error or a skipped March is just as possible, and the table catches the skipped file too.

The checks assume sales are zero or positive. A negative amount is flagged, so if your exports include refunds, decide how to treat them before relying on this table. The SKU length check assumes four-character codes, so change the 4 to match yours.

Adding April

Before you refresh with April’s file in the folder, select row 5 and insert a row, so TOTAL_CONTROL moves to row 6. Type April’s file name, expected rows, and expected sales in A5:C5, and copy D4:H4 down to D5:H5.

The total row needs one more edit. The new row sits just below the old A2:A4-style ranges, so Excel won’t stretch them to include it. Change every A2:A4, B2:B4, C2:C4, D2:D4, G2:G4, and H2:H4 range in row 6 to end at row 5. Then refresh with Data > Refresh All. All four file rows and the total should say RECONCILED.

If you refresh first, April’s rows have no line in the audit, and the total flags them as an unlisted file. That’s the warning working.

Frequently Asked Questions

Can I click Combine & Load right away?

Only when the folder holds nothing but the files you want, which is Microsoft’s own condition. A dedicated folder for the exports helps, but filtering costs a minute and protects you when someone saves a copy there.

Why not just delete Source.Name?

Without it, you can’t tell which file a row came from, so you can’t check each file’s count and total. Keep it in the loaded table, even if you hide it in reports.

Do I still need to check leading zeros?

Yes, any time a code column is typed. Import a CSV into Excel without losing leading zeros covers the same problem for a single file.

What to Do Next

Before combining files, confirm they all describe the same thing, one row per order, as covered in What Does One Row Represent in Your Sales Spreadsheet?. Writing down each column and its type, as in Build a small data dictionary for your spreadsheet, makes a renamed header easier to spot. The audit tab is one of the control totals in The Small Business Data Audit Checklist. When the audit and the source system still disagree, Why Your Business Numbers Don’t Match Across Systems covers where to look.

Copy-Paste Formula Reference

Row 5 (TOTAL_CONTROL), for three files. After adding April, change each 2:4 range to 2:5.

• B5: =IF(OR(COUNTA(A2:A4)=0, COUNT(B2:B4)<>COUNTA(A2:A4), COUNTIF(G2:G4,"CHECK INPUTS")>0), "CHECK INPUTS", SUM(B2:B4))
• C5: =IF(OR(COUNTA(A2:A4)=0, COUNT(C2:C4)<>COUNTA(A2:A4), COUNTIF(G2:G4,"CHECK INPUTS")>0), "CHECK INPUTS", ROUND(SUM(C2:C4), 2))
• D5: =COUNTA(Combined_Orders[Source.Name])
• E5: =COUNT(Combined_Orders[net_sales]) - COUNTIF(Combined_Orders[net_sales], "<0")
• F5: =ROUND(SUM(Combined_Orders[net_sales]), 2)
• G5: =IF(OR(B5="CHECK INPUTS", C5="CHECK INPUTS", COUNTIF(G2:G4,"CHECK INPUTS")>0), "CHECK INPUTS", ROUND(F5 - C5, 2))

H5:

=IF(G5="CHECK INPUTS", "CHECK INPUTS", IF(D5<>SUM(D2:D4), "STOP: UNLISTED OR STRAY FILE IN FOLDER", IF(D5<>B5, "STOP: TOTAL ROW COUNT MISMATCH", IF(E5<>D5, "STOP: CHECK SALES: EMPTY, TEXT OR NEGATIVE", IF(OR(COUNT(Combined_Orders[sku_code])>0, SUMPRODUCT(--(LEN(Combined_Orders[sku_code])<>4))>0), "STOP: SKU CONVERTED TO NUMBER / LENGTH MISMATCH", IF(OR(G5<>0, COUNTIF(H2:H4,"RECONCILED")<>COUNTA(A2:A4)), "STOP: UNRECONCILED ($" & TEXT(ABS(G5),"0.00") & " GAP)", "RECONCILED"))))))