Replace a monthly copy-paste process with Power Query, step by step
Use case · By Tahir Nas · Examples run in our simulator · Updated
10 min read
You replace a monthly copy-paste process with Power Query by saving every month's export into one folder, building a single query that reads, cleans and appends all the files in that folder, and loading the result where the old pasted range used to be. From then on, the monthly job is to drop the new file in the folder and select Refresh All.
Step 1: write down what you do now
A typical monthly routine looks something like this: open each store's export, copy the rows, paste them under last month's data in a master sheet, delete the header row that came with the paste, type the month into a new column, fix the dates that came through as text, and refresh a PivotTable. Each step is small. Together they take time every month and fail quietly when one is skipped.
Write your own version as a numbered list, then map each line to what Power Query will do instead:
| Manual step | Power Query replacement |
|---|---|
| Open each export file | Folder connector lists every file in one folder |
| Copy and paste the rows under last month | Combine Files appends all files in one step |
| Delete the repeated header row | Headers are promoted once per file, inside the combine |
| Type the month into a new column | A column taken from each file name |
| Fix dates and numbers that came through as text | One Changed Type step, with a locale if needed |
| Refresh the PivotTable | Refresh All reruns the query and the pivot (turn off background refresh on the query so the pivot waits for it) |
Keep the list. It is the checklist you will test the query against in step 5.
Step 2: put the files in one folder
Power Query works best when every month's export lands in the same place with the same layout. Create one folder, for example Sales exports, save each month's file into it with a consistent name such as sales-2026-09.csv, and keep anything else (notes, old versions) out of it. Microsoft's Folder connector documentation notes that files in subfolders are listed too, and that every file is processed the same way as the example file, so a stray file in a subfolder still ends up in the combine unless you filter it out.
Then connect to the folder: Data > Get Data > From File > From Folder, browse to the folder, and select Transform Data. Microsoft's combine files overview describes what you see: a table of files with a Content column holding each file's binary, and the advice to filter that list to just the files you need, for example by extension.
Selecting Combine Files on the Content column opens a dialog that uses one example file to decide how to read the rest. Microsoft explains that Power Query then creates a Transform Sample File query and a linked function, and applies that function to every file in the folder. Any cleaning you add to the sample query is applied to each file. The generated M has this shape:
let
Source = Folder.Files("C:\Reports\Sales exports"),
CsvOnly = Table.SelectRows(Source, each Text.Lower([Extension]) = ".csv" and Text.StartsWith([Name], "sales-")),
Parsed = Table.AddColumn(CsvOnly, "Data", each Table.PromoteHeaders(Csv.Document([Content], [Delimiter = ",", Encoding = 65001]), [PromoteAllScalars = true])),
Kept = Table.SelectColumns(Parsed, {"Name", "Data"}),
Expanded = Table.ExpandTableColumn(Kept, "Data", {"Date", "Store", "Product", "Units", "Price"})
in
ExpandedThe editor's own version uses a helper function rather than one inline step, but the logic is the same: list the files, keep the CSVs, read each one, and stack the results.
Step 3: tag each row with its month and set types
The month you used to type by hand is already in the file name. Take it from there, then set every column's type once. The block below runs the same logic on two small CSV files written inline, plus a stray notes file that the filter removes:
let
Files = #table(
type table [Name = text, Content = text],
{
{"sales-2026-08.csv", "Date,Store,Product,Units,Price#(lf)2026-08-03,Leeds,Kite,3,24.50#(lf)2026-08-17,York,Reel,1,9.99#(lf)"},
{"sales-2026-09.csv", "Date,Store,Product,Units,Price#(lf)2026-09-02,Leeds,Kite,2,24.50#(lf)2026-09-20,Leeds,Line,4,3.25#(lf)"},
{"notes.txt", "not data"}
}
),
CsvOnly = Table.SelectRows(Files, each Text.EndsWith([Name], ".csv")),
Parsed = Table.AddColumn(CsvOnly, "Data", each Table.PromoteHeaders(Csv.Document([Content], [Delimiter = ","]), [PromoteAllScalars = true])),
Tagged = Table.AddColumn(Parsed, "Month", each Text.Middle([Name], 6, 7), type text),
Kept = Table.SelectColumns(Tagged, {"Month", "Data"}),
Expanded = Table.ExpandTableColumn(Kept, "Data", {"Date", "Store", "Product", "Units", "Price"}),
Typed = Table.TransformColumnTypes(Expanded, {{"Date", type date}, {"Store", type text}, {"Product", type text}, {"Units", Int64.Type}, {"Price", type number}}, "en-GB")
in
TypedResult
| Month | Date | Store | Product | Units | Price |
|---|---|---|---|---|---|
| 2026-08 | 2026-08-03 | Leeds | Kite | 3 | 24.5 |
| 2026-08 | 2026-08-17 | York | Reel | 1 | 9.99 |
| 2026-09 | 2026-09-02 | Leeds | Kite | 2 | 24.5 |
| 2026-09 | 2026-09-20 | Leeds | Line | 4 | 3.25 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Csv.Document accepts text as well as a file's binary, which is what lets this example run without real files. The output is one table of four rows: two from August and two from September, each with its Month. notes.txt never reaches the combine. Text.Middle([Name], 6, 7) reads the seven characters after sales-, which is why a consistent naming pattern matters.
The last step passes a culture ("en-GB") so that dates and decimals are read the same way on every computer. The guide to changing type using a locale explains when that matters.
If the files are Excel workbooks rather than CSVs, the same pattern applies with Excel.Workbook in place of Csv.Document; the post on getting data from another Excel workbook shows how to pick the right table from each file.
Step 4: shape the report and load it
The old process usually ended in a PivotTable or a summary block. You can keep the PivotTable and point it at the query's output, or do the summary in Power Query:
let
Sales = #table(type table [Month = text, Store = text, Product = text, Units = Int64.Type, Price = number], {{"2026-08", "Leeds", "Kite", 3, 24.5}, {"2026-08", "York", "Reel", 1, 9.99}, {"2026-09", "Leeds", "Kite", 2, 24.5}, {"2026-09", "Leeds", "Line", 4, 3.25}}),
WithRevenue = Table.AddColumn(Sales, "Revenue", each [Units] * [Price], type number),
Summary = Table.Group(WithRevenue, {"Month", "Store"}, {{"Units", each List.Sum([Units]), Int64.Type}, {"Revenue", each List.Sum([Revenue]), type number}})
in
SummaryResult
| Month | Store | Units | Revenue |
|---|---|---|---|
| 2026-08 | Leeds | 3 | 73.5 |
| 2026-08 | York | 1 | 9.99 |
| 2026-09 | Leeds | 6 | 62 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Four detail rows become three summary rows: Leeds in August with 3 units and 73.50 revenue, York in August with 1 unit and 9.99, and Leeds in September with 6 units and 62.00.
Load the detail query as connection only if only the summary is read, and load the summary to a table where the pasted range used to be. The post on loading to a table or the Data Model helps if the detail is large.
Step 5: check the query against the last manual month
Do not switch off the old process until the new one has produced the same numbers. Take the totals from the last report you built by hand and compare them month by month:
let
Summary = #table(type table [Month = text, Store = text, Units = Int64.Type, Revenue = number], {{"2026-08", "Leeds", 3, 73.5}, {"2026-08", "York", 1, 9.99}, {"2026-09", "Leeds", 6, 62}}),
Manual = #table(type table [Month = text, Units = Int64.Type, Revenue = number], {{"2026-08", 4, 83.49}, {"2026-09", 10, 75}}),
ByMonth = Table.Group(Summary, {"Month"}, {{"Units", each List.Sum([Units]), Int64.Type}, {"Revenue", each List.Sum([Revenue]), type number}}),
Joined = Table.NestedJoin(ByMonth, {"Month"}, Manual, {"Month"}, "Manual", JoinKind.FullOuter),
Expanded = Table.ExpandTableColumn(Joined, "Manual", {"Units", "Revenue"}, {"Manual units", "Manual revenue"}),
Checked = Table.AddColumn(Expanded, "Matches", each [Units] = [Manual units] and Number.Round([Revenue], 2) = Number.Round([Manual revenue], 2), type logical)
in
CheckedResult
| Month | Units | Revenue | Manual units | Manual revenue | Matches |
|---|---|---|---|---|---|
| 2026-08 | 4 | 83.49 | 4 | 83.49 | true |
| 2026-09 | 6 | 62 | 10 | 75 | false |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
In this example August matches (4 units, 83.49) and September does not: the query has 6 units and 62.00, the manual figure 10 units and 75.00. The gap is exactly one Line row of 4 units at 3.25, which is what a row pasted twice looks like. A difference is not always the query's fault, so trace each one to the rows behind it before deciding which side is right. The guide to reconciling two tables goes further, matching row by row rather than by totals.
Step 6: the new monthly routine
Once the check passes, the routine becomes:
- Save the new export into the folder with the agreed name.
- Open the report workbook and select Data > Refresh All.
- Glance at the check query, or at row counts, before sending the report on.
If even that is too much, the post on refreshing Power Query automatically in Excel covers refresh on open and timed refresh, and the post on using an Excel cell as a Power Query parameter lets someone change the folder path without opening the editor.
What can still go wrong
A refreshable process fails differently from a manual one: loudly when a file breaks the pattern, and quietly when a file breaks it in a way no step checks. Watch for three things:
- A new or renamed column in one month's file. A new column is silently left out of the expand step; a renamed column comes through as
nullfor that month's rows; and later steps that name the old column can fail. The lesson on combining files whose columns differ shows how to handle it. - A file that doesn't follow the naming pattern. The month column comes out wrong. Filter the folder to names that match, and check for unexpected values in
Month. - The same month saved twice, for example
sales-2026-09.csvandsales-2026-09 (1).csv. Both files giveMonth = "2026-09", so both get combined and the month is doubled.
A small check query catches the last case by counting files per month on the folder listing:
let
Files = #table(type table [Name = text], {{"sales-2026-08.csv"}, {"sales-2026-09.csv"}, {"sales-2026-09 (1).csv"}}),
Tagged = Table.AddColumn(Files, "Month", each Text.Middle([Name], 6, 7), type text),
PerMonth = Table.Group(Tagged, {"Month"}, {{"Files", each Table.RowCount(_), Int64.Type}}),
Duplicates = Table.SelectRows(PerMonth, each [Files] > 1)
in
DuplicatesResult
| Month | Files |
|---|---|
| 2026-09 | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
It returns one row, 2026-09 with 2 files. An empty result means every month comes from exactly one file.
The refresh-proof checklist lists more checks worth adding once the basic query works.
Practise the whole process
The lesson end-to-end project: from raw export to monthly report walks through this kind of job in one place, and the challenge combine every monthly CSV in a folder practises the folder step on its own. Both run in the browser on Power Query Exercises, with the result checked against an expected table.
Frequently asked questions
Do the files have to be CSVs?
No. The Folder connector lists any files, and Combine Files picks a connector from the example file, such as Text/CSV, Excel or JSON, according to Microsoft's overview. All the files you combine should share the same type and layout.
What if each month arrives as a new sheet in one workbook instead of a new file?
Use the Excel workbook connector on that one file and filter its navigation table to the monthly sheets or tables, then combine them. The challenge append every monthly table in a workbook practises that version.
Can I keep the old master sheet as history?
Yes, but not as one file in the folder: the month is read from the file name, so a single history file would give every old row the same month. Either split the history into one file per month, named to the pattern, or load the master sheet as its own query that keeps its existing month column and append it to the folder query. Check the totals afterwards, because the old sheet may contain the manual errors the new process is meant to remove.
Sources
- Folder connector documentation (learn.microsoft.com)
- combine files overview (learn.microsoft.com)
- Csv.Document (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- How to refresh Power Query automatically in ExcelRefresh Power Query when an Excel workbook opens, every few minutes or on a timer with VBA, and make the queries safe to refresh when nobody is watching.
- Power Query vs VBA macros: when to switchWhen to replace a VBA macro with Power Query: which data jobs move across, which still need VBA, and how to migrate a macro without breaking the report.
- Why your Excel report breaks every month, and how Power Query fixes itMonthly Excel reports break when the export changes shape. See the usual causes and the Power Query steps that let a report survive each refresh.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.