# Power Query 清洗脏数据：把导出的脏表变成一张能反复刷新的干净表

搞清干净表的四条标准，以及表头错位、合并单元格、文本型日期和类型错误该怎么修，才能让刷新步骤稳定重放

> Power Query · 数据清洗 · 报表自动化 · 约 9 分钟 · 10 月 03 日

## 本篇要点

1. 清洗的终点是四条硬标准：第一行是列名、每行一条同类记录、每列一个含义一种类型、没有合并单元格。
2. 脏数据会让刷新出错，根本原因是步骤按列名引用列，并且每次刷新都原样重放；源文件结构一变，写死的“跳过 N 行”和列名引用就会错位或报错。
3. 合并单元格导入后只剩左上角有值、其余为空，用“向下填充”还原；但填充必须放在删掉小计合计行之后，否则小计行的空值会把上一行的值扩散下去。
4. 向下填充把行顺序当成了数据含义，源文件排序会变时，填充结果就会变，所以要么让上游稳定导出，要么填充后立刻显式排序。
5. 数据类型不是显示格式，而是值本身；文本型日期和数字能排序筛选，却算不了日期差、求和和用不了时间层次。
6. 文本转日期必须给解析规则：“使用区域设置”会写入 Table.TransformColumnTypes 的第三个参数，例如 en-US；不指定就用本机区域设置，换机器或云端刷新可能不同。
7. 对 CSV 这类非结构化来源，Power Query 会按前 200 行自动猜测，并在源步骤后插入“提升的标题”和“更改的类型”；这两步猜错时，后面的类型修改都无效，应删掉后在流程末尾统一改类型。
8. 类型转换失败的值显示为 Error；不要用“删除错误行”掩盖，应先用“保留错误”定位，再修源数据或加替换规则。
9. 单元格级错误通常不阻止加载，但会出现“已加载的查询包含错误”的提示，也有情况直接加载失败；进模型的列里不应该有 Error。
10. 删除顶部行本身不会报错，结构漂移通常要等到后面按列名找列的那一步才暴露，排查时要从“应用的步骤”最上面往下看。
11. 稳定的步骤顺序是：先定位行、再复原结构、最后统一改类型；同时尽量不改源列名，想固定输出结构用“选择列”。

---

上一篇讲到，自动化的本质是把“每周手动做一遍的动作”改写成“写一次、以后自动跑”的规则。这些话听起来很顺，但真正坐下来做的第一件事往往是这样：你从业务系统导出一个月销售明细，打开一看——前两行是标题和“导出日期”，第三行才是表头，中间夹着一行“小计”，日期列里混着 `2024/3/5` 和 `3/5/24`，金额列里有两个 `NA`。

这种表粘进 Excel、手工整理一遍，人人都能做。换成 Power Query，你的动作被记录成一串会反复重放的步骤，情况就完全不同了：这次改好的结果不值钱，值钱的是“以后每次拿到这种表，它都能自动变成干净的表”。

## 先确定终点：什么样的表才算“干净”

Power Query 眼里的干净表，判断标准很死板，就四条：

1. **第一行就是列名。** 上方没有标题行、说明行、单位行、空行。
2. **每一行是一条同样性质的记录。** 明细中间没有混进“小计”“合计”“数据来源：某某系统”这类行。
3. **每一列只有一个含义、一种数据类型。** 金额列里不该混着数字、“NA”“-”“待定”。
4. **没有合并单元格。** 靠“上一格管到下面几格”表达的从属关系，全部展开成每一行都有自己的值。

为什么终点定得这么硬？因为上一篇提到的第三步——建模，做的是“一对多的关系”。要建关系，得有主键，要有“这一行代表一件事”的确定性。一张带着小计行的表，连“一行是一个订单”都说不清楚，后面所有指标都会算错。

Excel 里你可以容忍这些脏结构，因为你用眼睛看、用手改。Power Query 没有眼睛，它只会执行你录下来的步骤。本篇要解决的，就是怎么让这些步骤既能修好这张表，又能在源文件略有变化时不崩。

## 为什么脏数据会让“这次刷对了、下次刷错了”

看一段 Power Query 自动生成的 M 脚本，机制就很清楚了：

```
源 = Excel.Workbook(文件),
删除的行 = Table.Skip(源, 2),
提升的标题 = Table.PromoteHeaders(删除的行),
保留的列 = Table.SelectColumns(提升的标题, {"订单日期", "客户", "金额"})
```

三件事同时发生：

- `Table.Skip(源, 2)` 写死了“跳过最前面 2 行”。它不知道表头在第几行，只知道上次表头在第 3 行。源里一旦有人多插一行说明，这一步就跳错。
- 后面的步骤是通过**列名**去找列的。列名变了，这一步直接报“找不到名为‘订单日期’的列”。
- 刷新时，这一串步骤会**原样从头再执行一遍**，不会有人现场判断。

所以清洗的目标不止是“让数据变干净”，还得让清洗动作对源文件的轻微波动有韧性。下面分两类讲最常见的四种脏。

## 第一类脏：表头不在第一行、有合并单元格

对表头位置：用“主页 → 删除行 → 删除最前面几行”，然后“主页 → 将第一行用作标题”。前者对应 `Table.Skip`，后者对应 `Table.PromoteHeaders`。这两步合起来的意思就是“把上面那些不是表头的行扔掉，把真正的表头提上来当列名”。

对合并单元格，要理解 Excel 导入时它变成了什么：一个合并区域里，**只有左上角那一格有值，其余格都是空（null）**。你看到的“一个值管了三行”，读进来是“一个值加三个空”。修法是在该列上用“转换 → 填充 → 向下”，规则是“本格为空就用上一格的值”，把值补齐。

**这里有一个顺序问题，弄反了会得到一批看起来正常、其实错误的数据。**

向下填充必须放在删掉小计行、合计行之后。原因很直接：小计行的某些列（比如日期、客户）是空的，如果先填再删，上一行的值会被填到小计行，再继续往下扩散；等你删掉小计行，被污染的值已经留在别的行了。正确的顺序是：**先扔掉不该存在的行，再补合并单元格留下的空。**

还有一个必须说清的边界：向下填充把“行的上下顺序”当成了数据含义的一部分。如果源文件的排序会变（有时按客户导、有时按日期导），填出来的结果会跟着变。所以要么让上游按稳定顺序导出，要么在填充后紧接着加一步显式排序。

## 第二类脏：文本型日期与类型错误

先纠正一个容易混淆的说法：**数据类型不是显示格式，是“这个值到底是什么”。**

在 Excel 单元格里，日期常常是“真日期加你设的显示格式”，Excel 在背后帮你转换了，所以你几乎不用关心这件事。但导出的 CSV、接口返回的文本里，日期就是一串字符。Power Query 不去猜，它需要一条明确的解析规则。

不转类型好像也能用：“2024-03-05”这种写法恰好逐位可比，排序筛选看着都对。但它**算不了日期差，也用不了把年、季度、月自动展开的时间层次**——这正是下一篇时间智能的前提。数字同理：文本型的金额列没法求和。

转日期有一条绕不开的规则：`03/05/2024` 到底是 3 月 5 日还是 5 月 3 日？靠“选中列 → 数据类型 → 使用区域设置”指定。它写进脚本的是第三个参数：

```
= Table.TransformColumnTypes(#"提升的标题", {{"订单日期", type date}}, "en-US")
```

`en-US` 告诉它按“月/日/年”解析，读成 3 月 5 日。不建议依赖默认值，因为默认用的是你本机的区域设置——换台电脑或在云端刷新，结果可能不一样。

**还有一个新手高频踩的坑：那两步“不是你自己加的”步骤。**

对 CSV、粘贴文本这类非结构化来源，Power Query 默认会根据**前 200 行**自动检测表头和类型，并在“源”步骤之后立刻插入“提升的标题”和“更改的类型”两步。它们是基于样本猜的，而且位置很靠前。猜错的时候，你后面再怎么改类型都没用：猜错的那一步已经先把值变成了错误。所以看到莫名的错误值，先回到“应用的步骤”里找这两步，删掉，然后在清洗流程的最后自己加一次类型步骤。

类型转换不成功的值会显示成 `Error`。这里给出两条实用规则。

**第一，不要用“删除错误行”来消灭错误。** 先用“主页 → 保留行 → 保留错误”把它们挑出来，看看是哪几行、长什么样，再决定是回去修源数据，还是加一条规则（比如把 `NA`、`-` 统一替换成空）。删除错误行意味着每次刷新都静悄悄少几行，而你不知道少了什么。

**第二，进模型的列里不该有 Error。** 单元格级的错误一般不会阻止数据加载，但你会在 Desktop 里看到“已加载的查询包含错误”的提示；也有情况会直接加载失败。更重要的是，错误值本身没法参与计算，还会让“这次刷新到底正不正常”这件事失去判断依据。

顺带说一个很省事的前置检查：粘贴和导出带出来的不可见字符、全角空格、数字首尾的空格，都会让文本转数字或日期失败。先用“转换 → 格式 → 清除（Clean）”和“修整（Trim）”清一遍，再定类型，能省掉一大半莫名其妙的错误。

## 让步骤稳稳重放的几条规则

**顺序规则：** 先定位行（删掉表头之上的说明、小计、页脚）→ 再复原结构（提升标题、拆分、向下填充）→ 最后统一改类型。类型步骤放最后，是因为前面还会增删行，类型设置不用反复重来；而且类型步骤也是按列名定义的，等列名定稳了再写更可靠。

这里要提醒一个容易误判的地方：**删除顶部行本身不会报错**，它只是老老实实跳过你当初设定的那几行。源结构一旦漂移，错误往往要等到后面按列名找列的那一步才暴露出来，看到的现象是“找不到列”，而真正的原因在最前面。刷新报错时，从“应用的步骤”最上面往下逐个点开看，比在最后一步纠结有用得多。

**列名规则：** 尽量不改源列的名字；要改就一次改完，因为改完之后所有步骤引用的是新名字。如果上游爱改列名，这里就是你的断点——所以连接数据源时，优先连数据库视图或者你能控制模板的导出文件，而不是随手导出的表。

**“删除列”和“删除其他列”不是一回事：** 显式删除某一列时，源里以后新增的列会照常出现在预览里；而“删除其他列”的意思是“除了我选的都删掉”，源里新增的列会被一起删掉，你还不会注意到。想要固定的输出结构就用“选择列”把需要的列挑出来，但要有心理准备：源新增的列不会自动进来。

最后是长期维护的动作。每次刷新后花半分钟做三件事：看每个查询有没有错误图标、看行数和最新日期对不对、看有没有多出来的空列。源文件格式变了，通常只需要在“应用的步骤”里改一处，然后确认后面的列名引用还接得上。

## 一条最小可用的流水线

```mermaid
flowchart LR
  A["源文件：Excel / CSV / 数据库"] --> B["删除顶部说明行"]
  B --> C["将第一行用作标题"]
  C --> D["删掉小计与合计行"]
  D --> E["向下填充：补合并单元格"]
  E --> F["Clean / Trim 清理文本"]
  F --> G["用区域设置定日期与数值类型"]
  G --> H["关闭并应用，进入建模"]
```

这条线只做一件事：把一张脏表变成干净表，而且这七个动作全部是规则，没有任何一步是“我这次手工敲一下”。下一件事就是给这张干净表配模型——建关系，然后写第一批度量值。

## 术语表

- 干净表（明细表结构）：第一行是列名、每行一条同类记录、每列一个含义一种类型、没有合并单元格和小计行，是建模之前的起点。
- 提升标题（将第一行用作标题）：把某一行的内容变成列名，替代“删除前 N 行再手工重命名”的做法。
- 向下填充（Fill Down）：本格为空就用上一格的值补上，用来还原合并单元格留下的空；代价是结果依赖行顺序，所以必须在小计行被删掉之后再执行。
- 使用区域设置（Using Locale）：转换文本型日期或数字时，指定按哪种区域规则解析（如 en-US），相当于给 Table.TransformColumnTypes 加第三个参数。
- 单元格级错误（Error 值）：某个值无法转换成指定类型时留下的标记；它不阻止大多数加载，但会提示“查询包含错误”，且进模型的列里不应该存在。
- 自动检测类型步骤：非结构化来源在前 200 行样本上猜出来的“提升的标题”和“更改的类型”两步，位置很靠前，猜错时要先删掉它们。

## 来源

1. [Microsoft Learn：使用 Power Query 时的最佳做法 — 尽早筛选、按步骤拆查询、用“删除底部行”“选择列”应对行数与列数变化的源](https://learn.microsoft.com/zh-cn/power-query/best-practices)
2. [Microsoft Learn：Dealing with errors（处理错误）— 单元格级错误不阻止加载、数据类型转换错误、删除/替换/保留错误三种处理方式](https://learn.microsoft.com/en-us/power-query/dealing-with-errors)
3. [Microsoft Support：Handling data source errors（Power Query）— 重命名列、删除列与删除其他列、替换值在刷新时的风险](https://support.microsoft.com/en-us/excel/handling-data-source-errors-power-query)
4. [Microsoft Support：Add or change data types（Power Query）— 数据类型清单、使用区域设置对话框，以及非结构化来源按前 200 行自动检测并插入两步的行为](https://support.microsoft.com/en-us/excel/add-or-change-data-types-power-query)
5. [Microsoft Learn：Error handling（Power Query）— Error 值的结构，以及用 try … otherwise 按自己的规则兜底](https://learn.microsoft.com/en-us/power-query/error-handling)

---

原文：https://pangzhengboyin.com/articles/power-query-clean-messy-data-refresh-6c3efc3e

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具。说出想学什么，它会先了解你的基础，再把主题写成一篇篇 5–10 分钟能读完的文章；边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com