Build a Power Query Workflow That Combines and Cleans Monthly Excel Files

You get numbered Power Query steps with paste-ready M code, a full query, and a flag on every step that breaks when column names change.

Prompt

Prompt
You are a senior Excel analyst who builds Power Query solutions for finance and operations teams.

Your job: help me build a Power Query workflow that combines monthly files from one folder into a single clean table that refreshes with one click.

Before you start, ask me these questions in one message and wait for my answers:
1. What file type are the monthly files (xlsx or csv), and how are they named?
2. What are the column headers, and do they ever differ between months?
3. What cleaning is needed (blank rows, duplicates, totals rows, numbers stored as text)?
4. Which Excel version do I use (Microsoft 365, 2021, 2019, Mac)?

Rules:
- Number every step and give the exact M code for it, ready to paste into the Advanced Editor.
- For each step, state whether it breaks if a column is renamed, added or removed, and give a safer alternative when one exists.
- Prefer steps that do not hard-code column names (trim headers, map renames, ignore missing fields).
- Add a SourceFile column so every row can be traced to its month.
- Use only the column names from my answers; do not invent columns.

Output format:
1. Setup checklist (folder and file rules)
2. Steps table: Step | What it does | M code | Breaks on column change? | Safer option
3. Full query as one let...in block
4. Monthly refresh routine (3-5 bullets)

End with one line on your assumptions and what you could not check without seeing the files.

Example output

At a glancePower Query steps to combine monthly sales filesSteps 8 and 10 break if column names change
  1. Read the folderSteps 1-2Load C:\Reports\Sales, keep xlsx, skip ~$ temp files
  2. Load and stack monthsSteps 3-4First sheet per file, tag SourceFile, combine
  3. Fix headersSteps 5-6Trim header spaces, map Amt to Amount
  4. Clean rowsSteps 7-9Drop Notes, remove blanks (breaks on rename), dedupe
  5. Set data typesStep 10Apply last via UI; breaks on column rename

Example for: a sales ops analyst combining monthly xlsx exports (Sales_2024-01.xlsx etc.) with columns Order ID, Order Date, Region, Rep, Amount, Notes; February's file used "Amt" and some headers have trailing spaces. Microsoft 365.

1. Setup checklist

  • One folder only: C:\Reports\Sales
  • Each file has one sheet with headers in row 1
  • No summary or archive files in the folder
  • Close files before refreshing (open files create ~$ temp copies)

2. Steps

StepWhat it doesM codeBreaks on column change?Safer option
1Reads the folderFolder.Files("C:\Reports\Sales")NoStore the path in a named cell
2Keeps xlsx, skips temp filesTable.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$"))No—
3Loads first sheet, tags SourceFilesee full queryNo—
4Stacks all monthsTable.Combine(AddData[Data])No, but renamed columns split into twoSteps 5-6
5Trims header spacesTable.TransformColumnNames(Combined, each Text.Trim(_))No—
6Maps Amt to AmountTable.TransformColumnNames(CleanNames, each if _ = "Amt" then "Amount" else _)NoAdd new variants to the if chain
7Drops NotesTable.RemoveColumns(FixAmount, "Notes", MissingField.Ignore)No (missing field ignored)—
8Removes blank rowsTable.SelectRows(DropNotes, each [Order ID] <> null)Yes, if Order ID is renamedAdd it to step 6 mapping
9Removes duplicatesTable.Distinct(NoBlanks)No—
10Sets data typesSelect columns, Transform > Data TypeYes, the generated formula lists every column nameApply types last, after renames

3. Full query

let
    Source = Folder.Files("C:\Reports\Sales"),
    XlsxOnly = Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$")),
    AddData = Table.AddColumn(XlsxOnly, "Data", each let f = [Name] in Table.AddColumn(Table.First(Excel.Workbook([Content], true))[Data], "SourceFile", (r) => f)),
    Combined = Table.Combine(AddData[Data]),
    CleanNames = Table.TransformColumnNames(Combined, each Text.Trim(_)),
    FixAmount = Table.TransformColumnNames(CleanNames, each if _ = "Amt" then "Amount" else _),
    DropNotes = Table.RemoveColumns(FixAmount, "Notes", MissingField.Ignore),
    NoBlanks = Table.SelectRows(DropNotes, each [Order ID] <> null),
    Deduped = Table.Distinct(NoBlanks)
in
    Deduped

Add step 10 through the UI after loading.

4. Monthly refresh routine

  • Save the new export into C:\Reports\Sales with the same naming pattern
  • Data > Refresh All
  • Check row count per SourceFile in a quick PivotTable
  • If a new column appears blank, check for a renamed header and add it to step 6

Assumption: the first object in each workbook is the data sheet; if files contain named tables, filter by Kind = "Sheet" in step 3.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for file names, headers and Excel version first means the M code uses your real columns instead of generic examples.
  • A breaks-on-column-change flag for every step shows exactly where the query will fail next month.
  • Favoring header trimming and rename mapping keeps the query working when exports change slightly.

Use when you paste a new monthly export into a folder and rebuild the same report by hand. Have your file names, column headers and Excel version ready. Works in ChatGPT, Claude or Gemini.

Common edits

  • Switch to CSV files and ask for Csv.Document steps with delimiter and encoding settings.
  • Ask for an extra step that unpivots month columns or adds a Month column parsed from the file name.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets