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
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
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 like this
Build an Excel Sales Forecast Comparing Moving Average and FORECAST.ETS
You get a tested Excel sales forecast with paste-ready formulas for two methods, a MAPE comparison on held-out months, and a clear pick of the better method.
Calculate Year-over-Year and Month-over-Month Growth in Excel Without Misleading Percentages
You get a growth table, exact Excel formulas that handle zero and negative baselines and partial months, plain-language takeaways, and a list of percentages that would mislead.
Find Outliers, Duplicates and Bad Data in Your Spreadsheet Using IQR
Paste a dataset and get a row-by-row table of duplicates, missing values, impossible values and IQR outliers, each with a suggested fix and Excel formulas.
Write a Weekly or Monthly KPI Report Narrative from Your Excel Numbers
Turns your pasted KPI numbers into a report narrative where each metric states the change, the likely driver and a next action, with every claim tied to a figure.
Write SQL Queries from a Plain-English Question and Your Table Schema
Paste your schema and ask a question in plain English. You get a working SQL query, a line-by-line explanation, and a row-count check query to verify the results.
Clean and Standardize Messy Spreadsheet Data with Exact Formulas
Get a column-by-column cleaning plan with ready-to-fill formulas for spaces, case, dates and duplicates, plus a list of rows that need a manual check.