【数据分析BI】AI 设计数据模型与 Power Query 清洗

摘要：用 AI 帮你设计数据模型，再用 Power Query 把脏数据洗成能建模的干净表。

一、痛点引入

你有没有遇到过这种表：一个 Excel 工作表里，订单日期、产品名、地区、销售额、数量全挤在一起，金额列还是"¥12,345"这种带符号的文本，地区和产品名一遍遍重复。你想用它做数据透视表（PivotTable，Excel 里拖字段就能汇总的那张表）分析，结果卡得要命，想按"省份"和"类别"交叉汇总还得各种 VLOOKUP 拼。

更头疼的是，你心里大概知道"应该先把表拆开、建个模型"，但不知道怎么拆、拆几张、谁连谁。手动复制粘贴清洗几百行，眼睛都花了还容易错。

这一篇，咱们就用 AI 把"拆表设计"这半件事搞定，再用 Power Query（Excel / Power BI 里那个"数据获取与转换"的清洗工具）把脏数据洗成能直接建模的干净表。洗完你就能顺手做透视、写度量值。

二、目标产出

学完这一篇，你手里会有三样东西：

一份 AI 设计的星型模型：清楚哪张是事实表（fact table）、哪几张是维度表（dimension table）、主键外键怎么连。
一段 真实可运行的 M 代码：完成"追加→改类型→条件列→自定义列→合并查询"的完整 ETL（ETL = 抽取 Extract、转换 Transform、加载 Load，把原始数据先洗一遍再搬进模型的整套动作）清洗。
一个 干净的数据模型：可以直接丢进数据透视表或 DAX 度量值里用，不卡、不乱。

环境假设：Excel 2016 及以上 / Excel 365 / Power BI Desktop 都通用。下面所有 M 代码在这些版本里行为一致（M 语言是 Power Query 背后那门公式语言，咱们在界面里点的每一步，其实都会被翻译成一段 M 代码）。

三、案例实战

咱们拿一份"销售数据"开刀。原始情况是：三个月的销售明细分别躺在三张 Excel 表（一月、二月、三月）里，字段是：订单日期、订单号、产品编号、地区编号、销售额、数量。其中销售额是"¥12,345"这种文本，产品编号、地区编号也是文本；产品名/类别、地区名/省份另有两张维表。

3.1 第一步：让 AI 帮你设计星型模型

先别急着写代码。星型模型（star schema）＝中间一张事实表，四周连着几张维度表，从上往下俯视就像一颗星星的光芒。为什么要用它？因为事实表（fact table）只放"每笔业务的数字 + 关联钥匙（外键）"，维度表（dimension table）放"看数据的角度"（产品、地区等，仅作示例角度），这样表更小、查询更快，透视和度量值才好写。

把下面这段话直接复制丢给任意 AI：

提示词：我有一份销售数据，原始字段有：订单日期、订单号、产品编号、地区编号、销售额、数量；另有产品维表（产品编号、产品名、类别）和地区维表（地区编号、地区、省份）。请帮我设计星型模型：① 指出事实表和维度表分别有哪些；② 列出每张表的字段，标注主键（PK）和外键（FK）；③ 用一句话说明表与表怎么连。

AI 大概率会给你这样的设计（咱们核对一下对不对）：

销售事实表（事实表）：订单号(PK)、订单日期、产品编号(FK)、地区编号(FK)、销售额、数量、销售等级、单价

注：本篇为简化，订单日期（OrderDate）暂留在事实表里，未单独建日期维度表。正规做法是另建一张 Date 维度表并连关系，留作 L4 延展。

产品维度表（维度表）：产品编号(PK)、产品名、类别
地区维度表（维度表）：地区编号(PK)、地区、省份

连接关系：事实表.产品编号 → 产品维度表.产品编号；事实表.地区编号 → 地区维度表.地区编号。

粒度假设：事实表一行 = 一笔订单（以订单号为主键），维度表一行 = 一个产品 / 一个地区；维度都按"编号"关联，不在事实表里直接塞产品名、地区名——这正是星型模型"瘦事实、胖维度"的用意。

拿到这个清单，咱们就知道 Power Query 里要建几张查询、谁合并谁了。

3.2 第二步：用 Power Query 做 ETL 清洗

打开 Excel →「数据」→「从表格/区域」（或「获取数据」），把三张表都变成 Power Query 查询。下面每一步都给真实 M 代码，你在 Power Query「高级编辑器」里粘贴、或照着 UI 点出来都行。

（1）追加查询：把三个月叠成一张

M 里"追加查询"就是 Table.Combine，把结构相同的几张表上下拼起来：

powerquery
let
    源 = Table.Combine({一月销售, 二月销售, 三月销售})
in
    源

关键点：被合并的表列名、列数、顺序必须一致，否则会错位或报错。

（2）设置数据类型：把"文本数字"洗成真数字

原始销售额带"¥"和逗号、编号是文本，先把符号清掉再改类型：

powerquery
let
    源 = Table.Combine({一月销售, 二月销售, 三月销售}),
    // 先去掉金额里的货币符号、千分位逗号、空格（含全角空格），避免转数字时报错
    清金额 = Table.TransformColumns(源, {
        {"销售额", each Text.Trim(Text.Replace(Text.Replace(Text.Replace(Text.Replace(_, "¥", ""), ",", ""), " ", ""), "　", "")), type text}
    }),
    // 再把各列改成正确的类型
    改类型 = Table.TransformColumnTypes(清金额, {
        {"订单日期", type date},
        {"产品编号", Int64.Type},
        {"地区编号", Int64.Type},
        {"销售额", type number},
        {"数量", Int64.Type}
    })
in
    改类型

关键点：改类型一定要在清洗之后、算数之前做。类型不对，后面的除法、条件判断全错。注意：上面假设"销售额"列在原始导入时是文本型；若有个别单元格已是真数字，Text.Replace 会报错，需先用 try 包裹或统一先转 type text 再处理。注意：编号列（产品编号、地区编号、数量）要转 Int64.Type，前提是它们本身是纯数字文本；若编号里含字母（如 P001），转 Int64.Type 会报错，应统一保留 type text，并让事实表与维度表两边的编号列类型一致（都文本或都 Int64），否则后面的"合并查询"连不上。

（3）添加条件列：按销售额打等级

UI 里的"添加条件列"按钮背后生成的 Table.AddConditionalColumn 是没有官方文档的内部函数（参数顺序无保障，手写容易踩坑）。手写 M 时推荐用有官方文档的 Table.AddColumn + each if 等价实现，按顺序判断、第一个满足的就生效：

powerquery
let
    源 = 改类型,
    加等级 = Table.AddColumn(源, "销售等级", each
        if [销售额] >= 10000 then "A级"
        else if [销售额] >= 5000 then "B级"
        else "C级", type text)
in
    加等级

关键点：条件从上往下短路，所以 >=10000 必须写在 >=5000 前面，否则大单全被划成 B 级。

（4）添加自定义列：算单价

Table.AddColumn 是"自定义列"的 M 实现，第二个参数是一个 each 表达式：

powerquery
let
    源 = 加等级,
    加单价 = Table.AddColumn(源, "单价", each try [销售额] / [数量] otherwise null, type number)
in
    加单价

说明：这里"单价"是用 Power Query 自定义列在 ETL 阶段算好、随表一起存储的"派生列"，适合"每行固定派生"的属性。若要的是随筛选实时变的聚合（如"平均单价随切片器变化"），则更适合用 DAX 度量值（见 B22），别把两者搞混。

（5）合并查询：把维度信息补进事实表

"合并查询"在 M 里分两步——先 Table.NestedJoin 把维度表作为嵌套列连进来，再 Table.ExpandTableColumn 把要的字段展开：

powerquery
let
    源 = 加单价,
    // 用产品编号左连接产品维度，生成名为"产品信息"的嵌套表列
    连产品 = Table.NestedJoin(源, {"产品编号"}, 产品维度, {"产品编号"}, "产品信息", JoinKind.LeftOuter),
    // 只展开我们需要的产品名、类别两列
    展开产品 = Table.ExpandTableColumn(连产品, "产品信息", {"产品名", "类别"}, {"产品名", "类别"}),
    // 同理连地区维度
    连地区 = Table.NestedJoin(展开产品, {"地区编号"}, 地区维度, {"地区编号"}, "地区信息", JoinKind.LeftOuter),
    展开地区 = Table.ExpandTableColumn(连地区, "地区信息", {"地区", "省份"}, {"地区", "省份"})
in
    展开地区

关键点：合并前两边连接键的数据类型必须一致（文本"1"≠数字 1），否则连不上或连出空值。JoinKind.LeftOuter 表示"保留左表全部行"，是事实表补维度的标准做法。

把上面几段串起来，就是一张完整的「销售事实」查询。产品维度、地区维度两张维表各自用 Excel.CurrentWorkbook(){[Name="产品表"]}[Content] 加载后改好类型即可（代码见下方"附"）。

（附）两张维度表的 M（最简写法）

powerquery
// 查询名：产品维度
let
    源 = Excel.CurrentWorkbook(){[Name="产品表"]}[Content],
    改类型 = Table.TransformColumnTypes(源, {
        {"产品编号", Int64.Type}, {"产品名", type text}, {"类别", type text}
    })
in
    改类型

// 查询名：地区维度（结构同理）
let
    源 = Excel.CurrentWorkbook(){[Name="地区表"]}[Content],
    改类型 = Table.TransformColumnTypes(源, {
        {"地区编号", Int64.Type}, {"地区", type text}, {"省份", type text}
    })
in
    改类型

关键点：Excel.CurrentWorkbook(){[Name="..."]}[Content] 要求 Excel 里那是**已定义为"表格"**的区域（选中区域按 Ctrl+T），不是普通单元格区域，否则取不到。

（6）也可以让 AI 直接写 M

如果你懒得手写，把需求连表结构一起丢给 AI：

提示词：请写一段 Power Query 的 M 代码：源是 Table.Combine({一月销售, 二月销售, 三月销售})；先把"销售额"列的 ¥、逗号、空格去掉；再把订单日期改成 type date、产品编号/地区编号/数量改成 Int64.Type、销售额改成 type number；然后加条件列"销售等级"（>=10000 为 A级，>=5000 为 B级，否则 C级）；再加自定义列"单价"=销售额/数量；最后用产品编号 LeftOuter 合并"产品维度"并展开产品名、类别。请只输出 M 代码并加中文注释。

拿到的代码一定要在 Power Query 里跑一遍再信——AI 偶尔会把函数名拼错，下面"避坑"里会讲怎么快速验。

四、原理小结

Power Query 的 M 语言是一门"声明式"的语言：你写的不是"怎么循环处理每一行"，而是一长串步骤（step），每一步吃进一张表、吐出一张新表，上一步的输出就是下一步的输入，像水管一样串起来。星型模型则是这套清洗的"目的地"——它把重复的文本（产品名、地区名）从庞大的事实表里挪到小巧的维度表里，事实表只留数字和关联钥匙（FK），于是模型体积更小、关联更清晰，数据透视表和 DAX（数据分析表达式，在模型上写"总计/占比/同比"那种公式的语言）才能跑得又快又准。

五、避坑指南

合并查不到数？先查连接键类型。 文本型的"1"和数字 1 在 M 眼里不是同一个东西，合并前务必把两边键列类型统一（都改成 Int64.Type 或都保留 type text）。
追加查询报错或错位？查列名。 Table.Combine 要求被合并的表列名、数量、顺序完全一致；差一个就会把 A 列数据塞进 B 列。
条件列等级全错？看顺序。 Table.AddConditionalColumn 从上往下短路，范围大的条件（如 >=10000）必须写在范围小的（如 >=5000）前面。
改类型报"无法转换"？先清脏字符。 金额里的"¥"、逗号、全角空格会直接导致 type number 转换失败，记得先用 Text.Replace/Text.Trim 清掉。
改了列名后面全红？同步更新引用。 M 是顺序依赖的，你在前面把"销售额"改名成"金额"，后面所有 [销售额] 都会报"找不到列"，要一起改掉。

六、进阶延展

回看基础：如果 Power Query 还生疏，先回 B21（第一张报表）、B26（用 AI 写 Power Query M 查询做数据清洗），本篇是它的硬核进阶。
下一层：模型洗干净之后，真正发力的是写度量值——去看 B22（用 AI 写 DAX 度量值），用 DAX 在清洗好的星型模型上算"同比、环比、占比"，这才是 BI 的精髓。
再进一步：当数据量上百万行、或要天天自动刷新，可以把 ETL 搬到 Power BI 云端或数据库里做，Power Query 只负责轻量清洗——那是 L4 的课题。

🛠️ AI 设计数据模型与 Power Query 清洗