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

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

Example for: a sales coordinator merging 3 CSV and 2 Excel monthly exports in Excel 365, duplicates identified by order number.

1. Header mapping
Original nameFound inStandard name
Order NoJan.csv, Feb.csvOrder ID
Order #Mar.xlsx, Apr.xlsxOrder ID
Cust NameJan.csv, Feb.csv, May.csvCustomer
AmtJan.csvAmount
Sales AmountMar.xlsx, Apr.xlsxAmount
2. Clicks
  1. Put all files in C:\Sales\Monthly.
  2. Data tab > Get Data > From File > From Folder, pick the folder, click Transform Data.
  3. Home tab > Advanced Editor, replace everything with the code below, click Done.
  4. Select Order Date, Transform tab > Data Type > Date. Select Amount, set Data Type > Decimal Number.
  5. Home tab > Close & Load To > Table > New worksheet.
3. M code
let
    // Read every file in the folder
    Source = Folder.Files("C:\Sales\Monthly"),
    // Skip Excel lock files that start with ~$
    Kept = Table.SelectRows(Source, each not Text.StartsWith([Name], "~$")),
    // Map messy header names to one standard name
    StandardName = (n as text) as text =>
        let t = Text.Lower(Text.Trim(n)) in
        if t = "order no" or t = "order #" then "Order ID"
        else if t = "cust name" then "Customer"
        else if t = "amt" or t = "sales amount" then "Amount"
        else Text.Trim(n),
    // Open each file: CSV or first sheet of Excel
    Opened = Table.AddColumn(Kept, "Data", each
        if Text.Lower([Extension]) = ".csv"
        then Table.PromoteHeaders(Csv.Document([Content], [Delimiter=",", Encoding=65001]), [PromoteAllScalars=true])
        else Table.First(Excel.Workbook([Content], true))[Data]),
    // Rename headers inside each file
    Renamed = Table.AddColumn(Opened, "Clean", each Table.TransformColumnNames([Data], StandardName)),
    // Stack all files into one table
    Combined = Table.Combine(Renamed[Clean]),
    // Drop rows with no Order ID
    NoBlanks = Table.SelectRows(Combined, each [Order ID] <> null and [Order ID] <> ""),
    // Keep the first row for each Order ID
    NoDupes = Table.Distinct(NoBlanks, "Order ID")
in
    NoDupes
4. Refreshing

Drop June.csv into the folder, then Data tab > Refresh All. It breaks if a new file uses an unmapped header (add an else if line), the folder moves, or a workbook's data is not on the first sheet.

5. Checks
  • Sum rows across the five files (4,812) and compare with rows before deduping.
  • Filter Order ID for a known duplicate, such as SO-10457, and confirm one row remains.
  • Confirm no Amount values show as Error.

Assumptions: CSVs are comma-separated UTF-8 and each Excel file holds data on its first sheet; check which duplicate copy you want kept.

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

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 in Coding