Power QueryExercises

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 stepPower Query replacement
Open each export fileFolder connector lists every file in one folder
Copy and paste the rows under last monthCombine Files appends all files in one step
Delete the repeated header rowHeaders are promoted once per file, inside the combine
Type the month into a new columnA column taken from each file name
Fix dates and numbers that came through as textOne Changed Type step, with a locale if needed
Refresh the PivotTableRefresh 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
    Expanded
Shown for Excel and Power BI Desktop only: this code needs a data source or language feature the simulator doesn't run, so no output is shown.

The 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
    Typed

Result

MonthDateStoreProductUnitsPrice
2026-082026-08-03LeedsKite324.5
2026-082026-08-17YorkReel19.99
2026-092026-09-02LeedsKite224.5
2026-092026-09-20LeedsLine43.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
    Summary

Result

MonthStoreUnitsRevenue
2026-08Leeds373.5
2026-08York19.99
2026-09Leeds662

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
    Checked

Result

MonthUnitsRevenueManual unitsManual revenueMatches
2026-08483.49483.49true
2026-096621075false

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:

  1. Save the new export into the folder with the agreed name.
  2. Open the report workbook and select Data > Refresh All.
  3. 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 null for 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.csv and sales-2026-09 (1).csv. Both files give Month = "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
    Duplicates

Result

MonthFiles
2026-092

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

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

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.