[Spreadsheet]Clean Messy Data in One Click with AI

Summary: Use ChatGPT to plan the cleanup and Excel Power Query to turn a messy spreadsheet into clean data.

1. The Pain (30-second empathy)

You've definitely been there: a colleague dumps an Excel file on you and says, "Hey, tidy this up and send it back." You open it and—the header is made of merged cells, with "华东" stretched across three rows; the name column has entries like " 张三 " with stray spaces front and back; the hire-date column mixes three formats—"2023/1/5", "2023.01.05", "2023-1-5"; there's a column called "部门-工号" that really should be two columns but got crammed into one cell; and worst of all, two identical duplicate rows are hiding at the bottom.

Your heart sinks: if you fix this by hand, thousands of rows would take forever. Miss one stray space and the reconciliation won't balance. You've vaguely heard that Excel has something called Power Query (in the Chinese UI it's labeled "获取和转换" / Get & Transform; it's Excel's built-in "data-cleaning bench"—you click a few buttons, it remembers the steps, and next time the data changes you just hit "Refresh" to re-clean, no redo) that can handle this, but you don't recognize a single button in that ribbon.

Don't panic. This article teaches you a lazy shortcut: let ChatGPT figure out the cleaning order first, then you follow its checklist and click a dozen or so times in Power Query. AI does the thinking, you just do the clicking, and every step stays readable so you know what it's doing.

2. What You'll Get

After this article, you'll walk away with two things:

A clean table. "Clean" has a concrete standard: no gaps left by merged cells; no leading/trailing spaces in text fields; the date column is a real "Date" type (not text that merely looks like a date); columns that should be split are split; duplicate rows are gone.
A reusable cleaning flow (called a "query" in Power Query—query (查询) = a saved set of cleaning steps in Power Query, like a recipe). Next time the colleague sends another dirty table with the same structure, you don't redo anything; just right-click "Refresh".

By "one click," we don't mean a single button does it all. It means AI orders the steps for you so you just follow along—far faster and more reliable than memorizing Power Query buttons yourself.

3. Hands-on Case

Below we use a hypothetical sales roster as an example; its problems are about the same as yours. First, build it into a proper "Table" in Excel—I'll call it "表1" (Table1).

⚠️ Cancel merges BEFORE building the table (loud warning): Excel's "Table (Ctrl+T)" does not support merged cells. Be sure to first select the "地区" (Region) rows → Home → Merge & Center → Unmerge Cells (confirm every cell has its own value), then select the whole data range and press Ctrl+T (or Insert → Table) to create the table. Reversing the order (Ctrl+T first, then unmerge) will skew the table and lose values.

The dirty table looks like this:

地区	姓名	入职日期	部门-工号	销售额
华东（合并三行）	张三	2023/1/5	销售-1001	5000
（合并，空格）	李四	2023.01.05	销售-1002	6000
（合并，空格）	王五	2023-1-5	销售-1003	5000
华北	赵六	2023/2/1	市场-2001	7000
华东	张三	2023/1/5	销售-1001	5000

The last row is an exact duplicate of the first—that's the "duplicate row" we want to kill.

⚠️ The key step when entering the dirty table (to avoid a broken replacement later): When typing this table into Excel, first set the entire "入职日期" (Hire Date) column to Text format (select the column → right-click → Format Cells → Text), or add an English single quote ' before each date (e.g., '2023/1/5). Otherwise Excel stores 2023/1/5 as a real date (a serial number), and when you reach step 5's "replace ./- with /", there'll be no characters left to replace and the whole step fails outright.

Step 1: Hand the dirty table to AI and let it produce a plan (prompt included)

Open ChatGPT (or any AI that can write code) and paste the whole prompt below. The key point of this prompt: tell the AI your column names and specific problems, and explicitly ask it not to invent Power Query features that don't exist.

You are an Excel Power Query expert. I have a dirty data table (the column headers are in Chinese); its structure and problems are as follows:
- 地区 (Region): merged cells in the header — the same value spans multiple rows (e.g., "华东" occupies three rows)
- 姓名 (Name): some cells have stray leading/trailing spaces, e.g., " 张三 "
- 入职日期 (Hire Date): formats are all mixed up — 2023/1/5, 2023.01.05, and 2023-1-5 all appear
- 部门-工号 (Dept-EmpID): should really be two columns, e.g., "销售-1001"
- 销售额 (Sales Amount): the table contains fully duplicated rows

Please give me two deliverables:
1) A Power Query cleaning checklist that a beginner can follow in order,
   with the exact menu path in the Power Query Editor spelled out for every step
   (e.g., Transform → Format → Trim).
2) The matching Power Query M code (the formula that lives in the "Advanced Editor"),
   with an English comment on every line explaining what it does.

Ground rules: do not invent features that don't exist in Power Query; if anything is uncertain, say so plainly —
no hype like "AI fixes it all in one click."

AI will most likely return a step checklist (remove blank rows, trim, unify dates, split columns, remove duplicates…) plus a chunk of M code. In step 2 below I turn that checklist into real, clickable operations—you can absolutely follow the AI's own checklist, or just follow what I wrote below.

Step 2: Follow along in Excel's Power Query

Pre-processing (final check before entering Power Query): When you built "表1" earlier, the "地区" column should already be unmerged (if you missed it, do it now: select the "地区" rows → Home → Merge & Center → Unmerge Cells). After unmerging, "华东" stays in the first cell and the two below go blank—we'll fill those in with "Fill" shortly.

Enter Power Query for real: Click Data → From Table/Range (in some versions, Data → Get Data → From Table/Range). Excel pops up the Power Query Editor (Power Query Editor = a window dedicated to tidying data; on the right is the "Applied Steps" pane, and every button you click adds a line there).

Now operate in cleaning order; each step is labeled with its menu path:

Use the first row as headers: Power Query usually detects this automatically. If not, click Home → Use First Row as Headers.
Restore the values broken by merged cells: Select the "地区" column → Transform → Fill → Down. "Fill Down" = copies the value from the cell above into the consecutive blank cells below, so "华东" fills the two rows beneath it.
Remove leading/trailing spaces from names: Select the "姓名" column → Transform → Format → Trim. "Trim" = removes only the extra spaces at the very start and end, keeping normal spaces in the middle. If you suspect invisible garbage characters (like line breaks copied from a webpage), click Transform → Format → Clean once more ("Clean" = removes those non-printing characters).
Unify the date format (the easiest to trip on—see Section 5): First unify the separators. Select the "入职日期" column → Transform → Replace Values, replace "." with "/"; then replace once more, replacing "-" with "/". Now they all look like "2023/1/5". Then select the column → Transform → Data Type → Date. If it still errors, switch to Transform → Data Type → Using Locale, choose "Chinese (China)" as the locale and "Date" as the type.
Split "部门-工号": Select the column → Transform → Split Column → By Delimiter → choose "Custom" as the delimiter, enter "-" → OK. Two columns are generated, named by default "部门-工号.1" and "部门-工号.2"; right-click the column header and rename them "部门" and "工号".
Remove duplicate rows: Click Home → Remove Rows → Remove Duplicates. When no column is selected, only rows where all fields are identical count as duplicates—exactly what kills that duplicate 张三.
Remove any leftover blank rows: Home → Remove Rows → Remove Blank Rows.

Finally click Home → Close & Load. The clean table returns to a new worksheet in Excel. Next time the colleague sends another dirty table with the same structure, just swap the data source and right-click Refresh—the whole cleaning reruns automatically.

Appendix: the corresponding Power Query M code (for those who want to go further, this lives in the "Advanced Editor"; it maps one-to-one to the 7 steps above; the table name "表1" must match the table name you created with Ctrl+T):

powerquery
let
    // 1. 从 Excel 表格"表1"读取数据
    //    （数据已是 Ctrl+T 正式表，[Content] 会把首行直接当表头、列名就是"地区/姓名/…"，
    //     所以无需再 Table.PromoteHeaders，这一步在从正式表导入时反而多余）
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],

    // 2. 把"地区"列的空值向下填充，补回合并单元格断掉的值
    向下填充 = Table.FillDown(源, {"地区"}),

    // 3. 去掉"姓名"列前后空格，并清除不可见字符
    修整姓名 = Table.TransformColumns(向下填充,
        {"姓名", each Text.Clean(Text.Trim(_)), type text}),

    // 4. 先把日期分隔符统一成 "/"
    替换点 = Table.ReplaceValue(修整姓名, ".", "/", Replacer.ReplaceText, {"入职日期"}),
    替换横线 = Table.ReplaceValue(替换点, "-", "/", Replacer.ReplaceText, {"入职日期"}),

    // 5. 把"入职日期"改成真正的日期类型（指定中文本地，避免月日颠倒）
    改日期类型 = Table.TransformColumnTypes(替换横线,
        {{"入职日期", type date}}, "zh-CN"),

    // 6. 按 "-" 把"部门-工号"拆成"部门"和"工号"两列
    拆分部门工号 = Table.SplitColumn(改日期类型, "部门-工号",
        Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"部门", "工号"}),

    // 7. 删除整行重复
    删除重复项 = Table.RemoveDuplicates(拆分部门工号),

    // 8. 删除完全为空的行
    删除空行 = Table.RemoveEmptyRows(删除重复项)
in
    删除空行

4. How It Works (Principle recap)

In one sentence: Power Query doesn't "modify the original table"; it records a cleaning recipe for you—every button you click in the editor adds a line of M formula to the "Applied Steps" pane on the right (M is Power Query's dedicated formula language, like the function formulas in Excel). AI's role is to help you get the recipe's order right and complete; you just follow along, which is like writing the recipe line by line. Because the entire flow is recorded, swapping the data source and clicking "Refresh" reruns the recipe automatically—dirty data stays dirty, but a clean result is always one refresh away.

5. Pitfall Guide

Cancel merges first, then fill. Power Query reading merged cells directly loses values—it only recognizes the top-left cell and treats the rest as blank. Always unmerge in Excel first, then use "Fill → Down" to restore the values.
Don't force dates with "Change Type". When three formats are mixed, converting straight to "Date" type will surely error. First unify the separators (Replace Values), or use "Using Locale" to specify the Chinese locale; if you truly hit a month/day order conflict like "2023/1/5" vs "05/01/2023", Power Query can't save you either—you'll need to confirm with the person who produced the table which side is the month.
Two kinds of spaces, two different tools. A plain " 张三 " is removed with "Trim"; but invisible characters like line breaks or non-breaking spaces copied from a webpage/PDF can't be removed by "Trim"—you need "Clean". Using both together is safest.
Splitting columns renames them. After splitting, the default names are "xxx.1" and "xxx.2"; downstream references to the original column name will error. Remember to rename manually so the names stay consistent with your reports and formulas.
Remove Duplicates looks at "whole rows". When no column is selected, only rows where every field is identical count as duplicates. To dedupe by a single column (say by "工号"/ID), first select those columns then click Remove Duplicates, or write in M: Table.RemoveDuplicates(表, {"工号"}).

6. Going Further

Revisit L1's B1 "AI Helps You Read a Table: Data Standards 101": Before cleaning data, first clarify "what counts as clean, what counts as dirty"—it cuts your detours in half.
Next up, B3 "Use AI to Write DAX So Your PivotTable Can Do Math": Once the table is clean, the next step is PivotTable (数据透视表 = a table that summarizes data by dimension without writing formulas) and DAX (DAX = Data Analysis Expressions, the language for writing "math-capable" measures for your PivotTable), so the data truly speaks.
One level deeper: Save this cleaning as a "template query" and combine it with a macro (宏 = a sequence of operations in Excel that can be recorded and run automatically) for one-click refresh; see the VBA volume of this series.

🛠️ Clean Messy Data in One Click with AI