Combine and Clean Multiple CSV or Excel Files into One Table with Power Query
You get a header mapping table, numbered Power Query clicks, commented M code that fixes mismatched columns and duplicates, and refresh steps for new files.
Prompt
You are a data analyst who teaches Power Query to Excel users with no coding background. Help me combine several CSV or Excel files into one clean table.
Before answering, ask me these questions in one message and wait for my replies:
1. Are the files CSV, Excel, or both, and are they all in one folder?
2. Paste the header row from two or three files so you can see how they differ.
3. Which column or columns identify a duplicate row?
4. Are you using Excel or Power BI, and which version?
Rules:
- Use Get Data > From Folder so new files are picked up on refresh.
- Give each step as exact menu clicks, one action per step, naming the ribbon tab and button.
- Map every mismatched header to one standard name in a table before any code.
- Comment each line of M code in plain English.
- Remove duplicates only on the key columns I name, and say how rows with blank keys are handled.
Output format:
1. Header mapping table (Original name | Found in | Standard name)
2. Step-by-step clicks (numbered)
3. Full M code for the Advanced Editor
4. How to refresh when new files arrive, and what breaks it
5. Quick checks to confirm the row count is right
End with one line listing the assumptions you made and anything I still need to check.Example output
Tips and common edits
Why this works
- Asking for real header rows first means the rename logic matches your actual files instead of guessed column names.
- Using From Folder builds refresh into the design, so new monthly files are added with one click.
- Requiring commented M code and named ribbon clicks lets non-developers follow and fix each step.
Use this in ChatGPT, Claude or Gemini when monthly exports pile up in a folder. Have a sample header row from a few files ready and know which column marks a duplicate, such as an order number.
Common edits
- Ask for duplicates matched on two columns, such as Order ID plus Date.
- Add a step that keeps the source file name as a column for tracing rows.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Write an Excel VBA Macro That Cleans and Formats Your Recurring Report
You get a commented VBA macro built from your own column headers, with backup steps, install instructions and a post-run checklist.
Batch Rename Files in a Folder with a Script You Can Preview First
You get a PowerShell or Bash rename script that shows every old-to-new name before changing anything, writes an undo log, and includes plain run steps.
Build an Excel Formula from a Plain-English Description of What You Need
You get a working Excel formula, versions for older Excel and Google Sheets, a one-row worked example, and a table of the blank and error cases it handles.
Write a Google Apps Script to Automate a Repetitive Google Sheets Task
You get a commented Google Apps Script built around your sheet names, columns and timing, plus install steps, a test plan on a copy, and undo steps.
Find and Fix Data Quality Issues with Ready-to-Run SQL Checks
Paste your schema and get SQL queries that catch duplicates, NULLs, orphaned foreign keys and out-of-range values, each with what a bad result means and a fix.
Write a Safe Automation Script with Dry Run and Error Handling
Get a commented shell or Python script for your repetitive task, with a dry-run flag, clear error handling and a test command to try on sample files first.