Add a Month with Power Query: Automate Your Monthly GL Consolidation

If month-end consolidation means manually copying and pasting data from a new file into your report, there is a better way. In this tutorial, we walk through how to use Power Query’s From Folder feature to automatically combine monthly GL files into a single clean table. Once the setup is done, adding a new month is as simple as dropping a file into a folder and clicking Refresh. We cover the manual Append Queries approach first so the folder method really shines, and then we build a PivotTable report on top to see the whole workflow in action.

Exercise 1: Manually Appending Monthly Tables with Power Query

Let’s say we have three monthly GL tables sitting on the same worksheet, one for January, one for February, and one for March. Each table has the same six columns: Date, JE #, Dept, Account, Description, and Amount.

The manual approach is to load each table into Power Query one at a time. We click inside the first table, go to Data, and choose From Table/Range. Once Power Query opens, we select Close & Load To and pick Only Create Connection. We repeat this process for each remaining table until all three show up in the Queries & Connections pane as connection-only queries.

Now that each month has its own query, we can stack them together. We go to Data, Get Data, Combine Queries, Append. We select Three or more tables, move all three into the Tables to append list, and click OK.

Power Query stacks the rows and lines up matching column names automatically. The columns do not need to be in the same order across tables, but the column names must be consistent. We then Close & Load To a table on an existing worksheet, and bam:

All 18 rows from the three monthly tables are combined into one clean results table. This works well, but it requires creating a separate query for every single data source. If we have a dozen monthly files, that is a dozen queries to manage. There has to be a better way.

Exercise 2: Automating the Folder with Power Query From Folder

Now, it is time to make things more interesting. Instead of individual tables in Excel, consider the following: all monthly GL files are stored in one folder as CSV files, named consistently like 2026-01_GL.csv, 2026-02_GL.csv, and so on.

We can point Power Query directly at that folder. We go to Data, Get Data, From File, From Folder, navigate to the folder, and click Open. Power Query returns a list of the files it found. Instead of loading just the file list, we use the Combine button. We choose Combine & Load To, confirm the column structure on the sample file dialog, and load the results to a table on an existing worksheet.

We hit Enter, and bam:

Power Query loads all six files at once and adds a Source.Name column that identifies which file each row came from. That Source.Name column is a built-in audit trail, which is one of the best parts of this approach. Every row knows exactly which file it came from.

Building a Tie-Out Table to Verify the Data

Before we trust the data, we want to verify it. The worksheet already has a control totals table with the expected row counts and dollar amounts for each source file. We can write formulas against the combined table to confirm everything landed correctly.

For row counts, we use COUNTIFS. In case we are new to the COUNTIFS function, it returns the count of rows that meet one or more criteria. The function arguments are:

In case we are new to the COUNTIFS function, it returns the count of rows that meet one or more criteria. The function arguments are:

• criteria_range1: the range to evaluate against the first criterion
• criteria1: the condition that cells in criteria_range1 must meet
• criteria_range2, criteria2, …: additional range and criteria pairs (optional)

We write the following formula into cell E10 to count how many rows in the combined table belong to each source file:

=COUNTIFS(data[Source.Name],B10)

We fill the formula down the column and confirm that each source file has exactly six rows. Now let’s check the dollar amounts. For that, we use SUMIFS.

Before diving in, a quick look at the SUMIFS function. It returns the sum of values in a range where one or more conditions are met. The function arguments are:

• sum_range: the range of values to add up
• criteria_range1: the range to evaluate against the first criterion
• criteria1: the condition that cells in criteria_range1 must meet
• criteria_range2, criteria2, …: additional range and criteria pairs (optional)

We write the following formula into cell F10 to sum the Amount column for each source file:

=SUMIFS(data[Amount],data[Source.Name],B10)

We fill that formula down as well. After applying some number formatting, the tie-out table shows that the COUNTIFS and SUMIFS results match the expected values for January through June exactly. The July row shows zero because that file is not in the folder yet.

And that is how we can verify the consolidated data against known control totals. Mission accomplished on the verification side. Now let’s build the report and then bring in July.

Exercise 3: Adding a New Month and Refreshing the PivotTable

Now that we have our combined folder data table, it is time to build a PivotTable report on top of it. We click inside the data table, go to Insert, PivotTable, and place the report on an existing worksheet. We put Dept in Rows, Amount in Values, and Date in Columns. The columns display as months automatically.

The report looks great, January through June, broken out by Admin, Ops, and Sales. Now the question everyone is thinking: what happens when July arrives?

All we need to do is drop the July CSV file into the source folder. No Power Query editing. No new queries. Just copy the file in.

With the July file in place, we go to Data and click Refresh All. Power Query goes to the folder, finds the new file, reads it, and folds it into the combined table. We hop over to Exercise 2 to check the tie-out, and bam:

The July row now shows 6 rows and $49,200.00, matching the expected control total perfectly. We switch back to Exercise 3, click Refresh All one more time, and the PivotTable updates automatically.

July just pops right in. A new column appears, the grand totals update, and the report is complete. No copy and paste. No new queries. Just drop the file in the folder and click Refresh. AND that is the power of the From Folder approach.

Summary

In this tutorial, we explored two Power Query approaches for combining monthly GL data. Exercise 1 showed the manual Append Queries method, where each data source gets its own connection-only query before they are stacked together. It works, but it does not scale well as more months arrive. Exercise 2 introduced the From Folder approach, which points Power Query at a directory of CSV files and combines them automatically, complete with a Source.Name audit trail column. We used COUNTIFS and SUMIFS formulas to tie the imported data back to known control totals. Exercise 3 showed how adding a new month is as simple as dropping a file into the folder and clicking Refresh All, with a PivotTable report updating automatically on top of the refreshed data.

If you have any suggestions, improvements, alternatives, or questions, please share by posting a comment below … thanks!

Sample File

FAQs

Does the From Folder approach work with Excel files as well as CSV files?

Yes, Power Query’s From Folder feature works with Excel workbooks, CSV files, text files, and several other formats. When combining Excel files, the Combine & Transform step will ask us to specify which sheet or table to pull from each file. As long as the structure is consistent across files, it works the same way.

What happens if a new file has a slightly different column order?

Power Query matches columns by name, not by position. So if a new file has the same column headers but in a different order, Power Query will still line everything up correctly. The column names do need to match exactly, including capitalization, so it is worth keeping file naming and column naming consistent.

Can we exclude certain files from the folder without deleting them?

Yes. In the Power Query editor, after clicking Transform Data on the file list step, we can filter the Name or Extension column to include only the files we want. For example, we could filter to keep only files that start with a certain prefix or end with .csv. Any file that does not match the filter is ignored during the load.

What is the Source.Name column and should we keep it?

The Source.Name column is added automatically by Power Query when using From Folder. It contains the file name of each source file, which gives us a built-in audit trail. We can use it in COUNTIFS and SUMIFS formulas to verify row counts and totals by file, as shown in Exercise 2. Whether to keep it in the final output depends on the use case, but it is very handy for reconciliation work.

Why did we need to click Refresh All twice to update the PivotTable?

This is a known quirk. The first Refresh All updates the Power Query data table. The PivotTable sometimes needs a second refresh to pick up the changes from the updated data table. Clicking Refresh All a second time, or right-clicking the PivotTable and choosing Refresh, resolves this. We can also set the workbook to refresh on open in the query connection properties.

Do the CSV files need to be in the same folder as the Excel workbook?

No, the source folder can be anywhere, including a shared network drive, a SharePoint library, or a local folder completely separate from the workbook. When we set up the From Folder connection, we specify the full folder path. If we ever need to move the folder, we just update the path in the query source step.

What if some months have extra columns that other months do not have?

Power Query will include all columns it finds across all files. Rows from files that do not have a particular column will show null in that column. This is usually fine, but it is worth reviewing the results after a refresh to make sure unexpected columns did not sneak in from a file formatted differently.

Is this approach better than using the manual Append Queries method from Exercise 1?

For recurring monthly processes, yes, the From Folder method is significantly better. It requires no changes to the Power Query setup when a new month arrives. The manual approach requires adding a new connection query and updating the Append query every single time. For one-time consolidations with data already in Excel tables, the manual append is perfectly fine.

Can we use COUNTIFS and SUMIFS to reference Power Query output tables the same way we reference regular Excel tables?

Absolutely. When Power Query loads data to a worksheet table, that table behaves just like any other Excel Table. We can reference it using structured table references in formulas, such as data[Source.Name] or data[Amount], exactly as shown in the COUNTIFS and SUMIFS examples in Exercise 2. This makes building tie-out and reconciliation formulas straightforward.