📎 配套资产 / Companion assets：本篇所有示例的可下载文件在仓库 配套资产/B26_M查询/——脏数据示例 CSV + 完整 8 步清洗查询（.pq）+ 每步前后对比说明，每个子目录都带中文 README 与 English README。 English: downloadable files for every example in this article live in 配套资产/B26_M查询/ — a messy sample CSV + the full 8-step cleaning query (.pq) + before/after notes, each with a bilingual README.

【办公开发】用 AI 写 Power Query M 查询做数据清洗

摘要：用 AI 帮你写 Power Query 的 M 代码，把重复清洗做成可复用的查询。

1. 痛点引入

你肯定遇过这种事：每月底系统导出的销售明细，永远是一张"脏表"——客户姓名前后带着空格、地区一会儿"华东"一会儿" 华东 "、销售额写成"¥1,200"这种带货币符号和千分位的文本、折扣写成"12%"、最右边还杵着一列用不上的"备注"。

你心里咯噔一下：这要是手动改，几百行得改到什么时候？改错一个空格，月底对账就对不上。你也隐约知道 Excel 里有个叫 Power Query（中文界面叫"获取和转换"，它是 Excel 自带的一个"数据清洗台"——你点几下按钮，它就记住步骤，以后数据变了点"刷新"就能重洗一遍，不用重做）的东西能搞定。

但真正拦住你的是第二步：那些清洗逻辑一旦稍微"不标准"（比如"折扣高于 20% 时提成按 3% 算"），光靠鼠标点按钮就点不出来了，得去改它背后那门叫 M 语言（Power Query 背后那门写清洗步骤的编程语言） 的代码。一看到 let...in、each、Table.AddColumn 这些，头就大了。

别慌。这篇文章就教你一个省事办法：让 AI 把 M 代码整段写出来，你只负责看懂、贴进去、刷新。AI 替你写代码，你只做验收，每一步还都能看明白在干嘛。

2. 目标产出

学完这篇，你能拿到两样东西：

一段真实可运行的 M 查询代码（不是伪代码、不是截图，是直接能贴进 Power Query 高级编辑器的完整 let...in）。它替你完成：挑列、去空格、把"¥1,200"变成数字 1200、按规则算提成、按销售额打 A/B/C 等级。
一套"说人话→出代码"的提示词模板。下回同事又甩来一张同结构的脏表，你把列名和规则改一改发给 AI，三分钟拿到新代码，右键"刷新"就搞定，不用重做。

所谓"用 AI 写 M 查询"，不是让 AI 替你点按钮，而是让 AI 直接产出 Power Query 背后那段代码——后者恰好是你自己最怕手写、又最该掌握的部分。

3. 案例实战

下面用一张假设的销售明细当例子。先在 Excel 里把数据建成一张"正式表格"（选中区域，按 Ctrl+T，或点插入 → 表格），我给它起名 RawData（这个表名等下要写进代码，注意大小写）。

脏表 RawData 长这样：

订单号	客户姓名	地区	销售额	折扣	备注
D001	张三	华东	¥1,200	12%	老客户
D002	李四	华北	¥8,500	25%	促销
D003	王五	华南	¥15,300	5%	大单
D004	赵六	华东	¥3,400	30%	复购
D005	钱七	华北	¥6,700	18%	新客

我们要把它洗成：丢掉"备注"、清掉文本空格、把带符号的数字转成真数字、新增"提成"列（折扣 > 20% 时按 3% 算，否则 5%）、新增"等级"列（销售额 ≥ 10000 为 A，≥ 5000 为 B，其余为 C）。

第一步：用 AI 生成 M 代码（附提示词原文）

打开 Kimi 或智谱清言（GLM）（或任何能写代码的 AI），把下面这段提示词整段粘进去。关键是：告诉它你的表名、列名、每一列的真实样子，以及清洗规则；并明确要求它"只用 Power Query 标准库函数，不要编造不存在的函数"。

你是一位 Power Query M 语言专家。请只使用 Power Query 标准库函数，不要编造不存在的函数。
我有一张 Excel 表格，名为"RawData"，列与示例数据如下：
- 订单号：文本，如 D001
- 客户姓名：文本，部分单元格前后有多余空格，如" 张三 "
- 地区：文本，部分带空格、大小写不一，如" 华东 "
- 销售额：文本，带货币符号和千分位，如"¥1,200"
- 折扣：文本，带百分号，如"12%"
- 备注：文本，不需要，要删掉

请用 M 语言写一个完整查询（let...in 结构），依次完成：
1. 从 Excel.CurrentWorkbook 读取名为 RawData 的表
2. 仅保留：订单号、客户姓名、地区、销售额、折扣
3. 地区：去掉首尾空格并转大写；客户姓名：去掉首尾空格
4. 销售额：去掉"¥"、逗号、空格后转成数字；折扣：去掉"%"和空格后转成数字
5. 新增"提成"列：用自定义函数计算，折扣 > 20 时 = 销售额×3%，否则 = 销售额×5%
6. 新增"等级"列：销售额 ≥ 10000 为"A"，≥ 5000 为"B"，否则为"C"
7. 按 订单号、客户姓名、地区、销售额、折扣、提成、等级 的顺序排好列
给出可直接粘贴进"高级编辑器"的完整代码，并逐行加中文注释。

AI 一般会回一段代码。咱们不要盲目照搬，先看懂、再校验。下面我给你一份我已经核对过、能跑的"标准答案"，你拿去对照——如果 AI 给的和这个思路一致，基本就能放心用。

第二步：照着标准答案，把 M 代码敲进去

完整 M 代码如下（直接复制即可，注意列名用的是中文，必须和你的源表逐字一致）：

powerquery
let
    // 1) 取数：从当前工作簿里名为"RawData"的表格读取
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],

    // 2) 只保留需要的列，丢掉"备注"
    KeepCols = Table.SelectColumns(Source, {"订单号", "客户姓名", "地区", "销售额", "折扣"}),

    // 3) 清洗文本：地区去空格并转大写；客户姓名去首尾空格
    TrimText = Table.TransformColumns(KeepCols, {
        {"地区", each Text.Upper(Text.Trim(_)), type text},
        {"客户姓名", each Text.Trim(_), type text}
    }),

    // 4) 把"销售额""折扣"从带符号的文本转成真正的数字
    ToNumber = Table.TransformColumns(TrimText, {
        {"销售额", each Number.FromText(Text.Remove(_, {"¥", ",", " "})), type number},
        {"折扣", each Number.FromText(Text.Remove(_, {"%", " "})), type number}
    }),

    // 5) 自定义函数：算提成（折扣高于 20% 时只给 3%，否则 5%）
    //    自定义函数＝你自己定义的一段可重复调用的小公式，用 (参数) => 表达式 的写法
    CalcCommission = (sales as number, discount as number) as number =>
        if discount > 20 then sales * 0.03 else sales * 0.05,

    // 6) 加一列"提成"，每行调用上面的自定义函数
    //    Table.AddColumn＝在表里新增一列，第三个参数是"对每一行算什么"的函数
    AddCommission = Table.AddColumn(ToNumber, "提成",
        each CalcCommission([销售额], [折扣]), type number),

    // 7) 条件列：按销售额给每行打 A / B / C 等级
    //    条件列＝用 if 判断给每行打标签的列；M 的写法是 if...then...else，没有 end
    AddLevel = Table.AddColumn(AddCommission, "等级",
        each if [销售额] >= 10000 then "A"
             else if [销售额] >= 5000 then "B"
             else "C"),

    // 8) 调整列顺序，输出最终结果
    Result = Table.ReorderColumns(AddLevel,
        {"订单号", "客户姓名", "地区", "销售额", "折扣", "提成", "等级"})
in
    Result

第三步：把代码贴进 Power Query 高级编辑器

操作路径（Excel 365 / 2021，界面基本一致）：

点数据 → 获取数据 → 从其他源 → 空白查询。
右侧"查询设置"上方点高级编辑器（图标长这样：{ }）。
把里面默认那两行（let Source = "" in Source）整段清空，粘进上面的完整代码。
点完成。如果列名、表名都对，会立刻预览出清洗后的结果。
想落地到工作表：点主页 → 关闭并上载。以后源表 RawData 变了，右键这个查询 → 刷新，就自动重洗。

第四步：读懂 AI 给的 M 代码（重点拆解）

怕 M 代码，主要是没人拆给你看。其实它就四块零件，认全了就不吓人：

let...in 是个"步骤清单"。你在 let 后面写的每一行（如 KeepCols = ...）都是一个"变量 = 这一步的结果"。in 后面写最终要输出的那个变量（这里是 Result）。Power Query 界面里你点的每一步，背后就是自动生成这样一行。
each 是"对每一行执行"的偷懒写法。each Text.Trim(_) 等价于 (_) => Text.Trim(_)，那个 _ 代表"当前这一行"。在 each 里面想取某列的值，就写 [列名]，比如 each [销售额] 就是取当前行的销售额。注意：[列名] 这种写法只能在 each 内部用。
Table.AddColumn 是"加一列"的标准姿势。第一个参数是原表，第二个是新列名（加英文双引号），第三个是"每行算什么"的函数（通常就用 each）。本文第 6、7 步都是它。
自定义函数用 => 不是 =。CalcCommission = (sales, discount) => ... 这一行定义了一个可重复调用的小公式；=> 左边是参数，右边是算法。它必须写在 let 里面、in 之前，才能被后面的步骤调用。

把这四块认熟，你再看 AI 给的任何一段 M 代码，都能顺着 let 一行行读下去。

第五步：刷新即复用

把查询上载到工作表后，它和源表 RawData 是"绑定"的。下个月新数据来了，你只要：

把新数据覆盖进 RawData 表（保持列名、表名不变）。
右键查询 → 刷新（或数据 → 全部刷新）。

清洗步骤一行都不用重做。这就是用 M 查询做清洗相比"手动改"最大的好处：规则写一次，反复用一辈子。

4. 原理小结

一句话：Power Query 你平时用鼠标点出来的每一步，背后都是一段 M 语言代码；所谓"高级编辑器"就是让你直接看到并改写这段代码的窗口。M 是一门函数式语言——数据从 Source 进去，经过 Table.SelectColumns、Table.TransformColumns、Table.AddColumn 等一个个"函数管道"逐级变形，最后由 in 交出成品。AI 的价值在于：它把"你得先懂 M 才能写 M"这道门槛，变成了"你用大白话把规则和列名说清楚，AI 就能产出合格代码"，而你只需要会读、会验收。

5. 避坑指南

列名必须用英文双引号，且和源表逐字一致。哪怕差一个空格、一个全角符号，M 都会报"找不到列"。中文列名照样写 "订单号"，但务必和 Excel 里的表头一模一样。
each 里取当前行字段用 [列名]，别写成单元格坐标。each [销售额] 是对的；在 each 外面（比如自定义函数体里）想取行字段，得用参数，例如 CalcCommission 里用的是传进来的 sales 而不是 [销售额]。
文本转数字前，一定先清掉符号。Number.FromText("¥1,200") 会直接报错；必须先 Text.Remove 掉 ¥、,、空格，再转换。这一个坑，新手十有八九踩。
自定义函数用 => 不是 =。(x as number) => x * 2 才是函数；写成 = 会被当成普通赋值，后面一调用就报错。参数类型（as number）可写可不写，但写上更稳、AI 也更不容易写错。
改完高级编辑器代码要点"完成"才算保存。只关闭窗口不点"完成"，改动会丢。之后源数据更新，要点"刷新全部"才会重跑整条查询，光改源表不会自动变。

6. 进阶延展

只想用鼠标点、完全不碰代码也能洗同样的数据？回看 B2《用 AI 一键清洗脏数据》——它走的是"AI 出清单、你照着点按钮"的路线，适合暂时不想写 M 的人。
洗完的数据想**建数据模型（data model，把多张表关联起来、形成一层可复用的"数据底座"）**再分析，看 B23《用 AI 搭数据模型》。模型搭好之后，配合 DAX（Power BI / Power Pivot 里写度量值的那门计算语言） 就能做动态汇总。
想把"刷新 + 导出"做成一键，可以学 macro（宏，Excel 里把一串操作录下来、之后自动重放的机制），相关实战见 B7《邮件合并 + VBA 导出 PDF》与 C1《周报自动化》。更进一步，从 VBA 里驱动 Power Query 查询结果，会触及 COM object（组件对象模型对象，VBA 用来操控 Excel 等其他程序的接口）——那是"办公开发"更深一层的话题。
清洗结果直接拖进 PivotTable（数据透视表，Excel 里那种拖拽字段就能汇总分析的表） 做月度看板，是这条链路最自然的下一站；想做得漂亮可结合 B24 的仪表盘思路。

小结：今天你拿到了"用 AI 写 M 查询做清洗"的完整打法——提示词出码、高级编辑器验收、刷新即复用。下一步，要么往 B23 建模型、用 DAX 算指标，要么往 C1 用 VBA 把流程彻底一键化。

🛠️ 用 AI 写 Power Query M 查询做数据清洗