Why your Excel report breaks every month, and how Power Query fixes it
Explainer · By Tahir Nas · Examples run in our simulator · Updated
11 min read
A monthly Excel report breaks because it quietly depends on the data arriving in exactly the same shape as last month, and real exports rarely do. Power Query fixes this by recording each cleaning step once and replaying it on every refresh, written so that it refers to columns by name and copes with new months, extra rows and foreign number formats.
Why monthly reports break
A formula-based report is a set of assumptions. SUM(C2:C40) assumes the data still ends on row 40. A VLOOKUP with column index 5 assumes the fifth column is still the one you want. A chart pointed at January to March assumes nobody added April. Each assumption held when you built the workbook, and each one can fail without warning the next time you paste in a fresh export.
The second source of breakage is the manual work around the formulas. Deleting the two title rows, fixing the dates that came through as text, trimming stray spaces in a status column: these steps live in your head, not in the file. Miss one and the numbers are wrong, often without any visible error.
Six changes that break a formula-based report
| What changed in the export | What happens to the formulas |
|---|---|
| A new month column appears | Totals and charts ignore it until you extend every range |
| The system adds a title or "exported on" row | Headers shift down a row and lookups return the wrong values |
| A column is renamed or moved | A rename goes unnoticed; a move makes position-based lookups pick up the wrong column |
| Amounts or dates arrive as text, or in another country's format | SUM skips them, or a date like 03/02 is read as 2 March instead of 3 February |
| More rows than last month | Fixed ranges cut off the newest data |
| A label is typed differently ("paid " instead of "Paid") | SUMIF and filters skip the rows that don't match exactly |
The common thread is that formulas point at positions: a cell, a row number, a column index. Exports change positions all the time.
What Power Query changes
Power Query records every transformation as a step and applies those steps again when the query runs. Microsoft's overview describes the result as "a repeatable process (query) that you can easily refresh in the future" (What is Power Query?, Microsoft Learn). The same page notes that Power Query doesn't modify the source data, so the raw export stays untouched.
Each step appears in the Applied Steps list, and selecting a step shows the data as it was at that point (Applied steps, Microsoft Learn). That list is the written-down version of the manual routine that used to live in your head. A query also reads the whole table or sheet, not a fixed range, so extra rows in next month's export are included on refresh without any change. If you want the mental model in more depth, the lesson applied steps are a recipe, not a one-off edit walks through it.
Recording steps is only half the fix. A recorded step can still point at a position, so the rest of this article covers how to write the steps that break most often in a way that survives next month's file.
Fix 1: new month columns are picked up automatically
Wide exports with one column per month are the classic monthly breakage. Instead of summing named month columns, unpivot everything except the key columns. Unpivot Other Columns keeps the columns you name and turns all the others into attribute and value pairs.
let
Source = #table(
{"Region", "Jan", "Feb", "Mar"},
{{"North", 120, 135, 150}, {"South", 90, 80, 110}}
),
Unpivoted = Table.UnpivotOtherColumns(Source, {"Region"}, "Month", "Sales"),
Totals = Table.Group(Unpivoted, {"Region"}, {{"Total", each List.Sum([Sales]), type number}})
in
TotalsResult
| Region | Total |
|---|---|
| North | 405 |
| South | 280 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
North totals 405 and South 280. Now run exactly the same steps on next month's export, which has gained an Apr column:
let
Source = #table(
{"Region", "Jan", "Feb", "Mar", "Apr"},
{{"North", 120, 135, 150, 160}, {"South", 90, 80, 110, 95}}
),
Unpivoted = Table.UnpivotOtherColumns(Source, {"Region"}, "Month", "Sales"),
Totals = Table.Group(Unpivoted, {"Region"}, {{"Total", each List.Sum([Sales]), type number}})
in
TotalsResult
| Region | Total |
|---|---|
| North | 565 |
| South | 375 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The totals move to 565 and 375 with no edit to the query. Microsoft's documentation says the same about the interface command: new date columns that arrive in a refresh "get picked up and unpivoted" (Unpivot columns, Microsoft Learn). The opposite option, Unpivot Only Selected Columns, names the month columns and leaves new ones untouched, so choose Other Columns when the months grow.
The lesson unpivot columns so new months are picked up covers turning the month text into real dates afterwards, and the exercise unpivot month columns into rows lets you practise the step.
Fix 2: banner rows above the headers
Many systems put a report title or an "exported on" line above the real header row. Remove those rows first, then promote the headers, so the column names come from the data rather than being typed in. Skipping by a condition (every row until the one whose first cell is "Product") survives a month where the system adds or drops a banner line, where a fixed count would not.
let
Source = #table(
{"Column1", "Column2"},
{{"Monthly sales report", null}, {"Exported 01/03/2026", null}, {"Product", "Units"}, {"Kites", "12"}, {"Reels", "7"}}
),
SkipBanner = Table.Skip(Source, each [Column1] <> "Product"),
Promoted = Table.PromoteHeaders(SkipBanner, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted, {{"Units", Int64.Type}})
in
TypedResult
| Product | Units |
|---|---|
| Kites | 12 |
| Reels | 7 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result has Product and Units as headers and two clean rows. The Remove Top Rows command in the ribbon only takes a fixed count, so the condition is a small edit in the formula bar. The lesson on removing report banners before promoting headers shows both versions.
Fix 3: numbers and dates in another country's format
An export from a European system often writes amounts with a decimal comma and dates day first. Changing the type without saying which format the text is in produces errors or, worse, swapped days and months. Table.TransformColumnTypes takes an optional culture for exactly this (Table.TransformColumnTypes, Microsoft Learn).
let
Source = #table(
{"Customer", "Amount", "Invoice date"},
{{"Acme", "1.250,50", "03.02.2026"}, {"Birch Ltd", "980,00", "28.02.2026"}}
),
Typed = Table.TransformColumnTypes(
Source,
{{"Amount", type number}, {"Invoice date", type date}},
"de-DE"
)
in
TypedResult
| Customer | Amount | Invoice date |
|---|---|---|
| Acme | 1250.5 | 2026-02-03 |
| Birch Ltd | 980 | 2026-02-28 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
With the German culture, "1.250,50" becomes 1250.5 and "03.02.2026" becomes 3 February 2026. Because the culture is part of the step, the conversion no longer depends on the regional settings of whoever opens the workbook. The lesson convert dates and numbers with a locale goes through the common cultures.
Fix 4: labels that almost match
A status column with "Paid", "paid " and "PAID" looks fine to a person and wrecks a filter. Power Query compares text exactly too, and case sensitivity in Power Query covers what that does to merges and groups. In a formula workbook the fix is usually a helper column someone forgets to copy down. In Power Query it is one step that runs on every row, every month.
let
Source = #table({"Order", "Status", "Amount"}, {{"A1", "Paid", 100}, {"A2", "paid ", 40}, {"A3", "Open", 60}, {"A4", "PAID", 25}}),
Cleaned = Table.TransformColumns(Source, {{"Status", each Text.Proper(Text.Trim(_)), type text}}),
Paid = Table.SelectRows(Cleaned, each [Status] = "Paid"),
Total = List.Sum(Paid[Amount])
in
TotalResult
165
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The paid total is 165, because orders A2 and A4 now match. Without the cleaning step it would be 100. The lesson on trimming, cleaning and capitalising text covers the other text functions you are likely to need.
Fix 5: when a column really changes, fail loudly
Some changes should stop the report. If the system renames "Amount" to "Net amount", you want to know. If renames are routine for a source, handling changing column headers shows how to absorb the known ones and still fail on the rest. A formula workbook carries on summing whatever now sits in that position, and if the column also moved, an index-based lookup silently picks up a different one. Power Query selects columns by name, so a renamed column raises an error at the step that uses it:
let
Source = #table({"Customer", "Net amount"}, {{"Acme", 100}, {"Birch Ltd", 250}}),
Selected = Table.SelectColumns(Source, {"Customer", "Amount"})
in
SelectedResult: an error
Expression.Error: The column 'Amount' of the table wasn't found.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The error is an Expression.Error that names the missing column, and the page on the column of the table wasn't found explains how to trace it. A visible error is better than a report that refreshes cleanly with the wrong numbers.
For a column that is genuinely optional, say so in the step. Table.SelectColumns accepts MissingField.UseNull, which adds the missing column filled with null instead of failing (Table.SelectColumns, Microsoft Learn):
let
Source = #table({"Customer", "Amount"}, {{"Acme", 100}, {"Birch Ltd", 250}}),
Selected = Table.SelectColumns(Source, {"Customer", "Amount", "Discount"}, MissingField.UseNull)
in
SelectedResult
| Customer | Amount | Discount |
|---|---|---|
| Acme | 100 | null |
| Birch Ltd | 250 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Use this only for columns that may legitimately be absent. Applied to every column, it hides the very changes you want to hear about. The lesson when a column goes missing compares the options.
What Power Query doesn't fix
Power Query removes the repeated manual work, but a few things still need a person:
- Someone still refreshes. The query runs when the workbook is refreshed. Microsoft's overview mentions manual refresh, scheduled refresh in products such as Power BI, and refresh through the Excel object model (What is Power Query?, Microsoft Learn). The post on refreshing Power Query automatically in Excel covers the options.
- The source has to be where the query expects. If the export is saved under a new name or in a new folder each month, point the query at a folder or a fixed file name. The guide to combining monthly files shows the folder pattern, and the post on replacing a monthly copy-paste process walks through it step by step.
- A changed meaning is invisible. If a column keeps its name but starts holding values in thousands, no query will notice. Validation steps that check totals or ranges help here.
- Steps recorded by clicking can still point at positions. An automatic "Changed Type" step can list every column by name, including ones that later disappear. Review the generated steps before trusting them.
A monthly routine that holds
- Save the new export with the same name, or into the same folder.
- Refresh the workbook.
- Check the row count and one known total against the source system.
- If a step fails, read the error, fix the step once, and the fix applies to every future month.
The refresh-proof checklist lists fifteen things to check before you hand a query to someone else, and the exercise debug a query that works today and breaks on refresh is a realistic practice run. Every example above was run in the Power Query Exercises simulator, and you can try each fix there with your own variations.
Frequently asked questions
Do I need to write M code to do this?
Mostly not. Unpivot Other Columns, Use First Row as Headers, Change Type Using Locale, Trim and Capitalize Each Word are all on the ribbon or right-click menus, and the editor writes the M for you. Two steps above need a small edit in the formula bar: skipping a varying number of banner rows, and the MissingField.UseNull option. Reading the generated M is worth learning anyway, because it is the quickest way to spot a step that points at a position.
Is Power Query available in Excel for Mac?
Microsoft's product table lists Excel for Mac alongside Excel for Windows as having the M engine and the Power Query Desktop experience (What is Power Query?, Microsoft Learn). Connector support differs by host, so check the specific source you need. The post on Power Query in Excel for Mac lists what works there.
Will Power Query change my original export file?
No. Power Query reads the source, applies the steps and loads the result where you choose in the workbook. The source data isn't modified.
Sources
- What is Power Query?, Microsoft Learn (learn.microsoft.com)
- Applied steps, Microsoft Learn (learn.microsoft.com)
- Unpivot columns, Microsoft Learn (learn.microsoft.com)
- Table.TransformColumnTypes, Microsoft Learn (learn.microsoft.com)
- Table.SelectColumns, Microsoft Learn (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 in Excel for Mac: what works and what doesn'tWhat Power Query in Excel for Mac can do, per Microsoft's documentation and Excel team posts: the editor, data sources, file paths, sharing with Windows.
- 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.
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.