Power Query——AI 驱动的数据清洗流水线

第10章：Power Query 数据清洗

如果你曾经花几个小时手工合并多个 Excel 文件、逐行删除多余数据、用 SUBSTITUTE 函数去掉单元格里乱七八糟的字符——那你一定要学 Power Query。它是 Excel 内置的数据清洗和整合引擎，能把你以前需要半天完成的工作压缩到5分钟，而且下次只需要点一下"刷新"。

Power Query 是什么

Power Query 是微软在 Excel 2016 之后内置的数据查询和转换工具（Excel 2010/2013 可通过插件安装）。它的核心能力是：把"如何处理数据"的步骤记录下来，下次数据更新时一键重放。

换句话说，你只需要设置一次，之后每次新数据进来，点一下"全部刷新"，清洗好的数据自动出现。这就是 Power Query 最大的价值：把一次性的手工劳动变成可重复的自动化流程。

传统方式 vs Power Query 方式

❌ 传统手工方式

收到12个月的销售文件，逐个打开

复制粘贴到汇总表，调整格式

手工删除小计行、合并单元格、空行

用公式或手动统一日期/数字格式

下月重复同样的操作……

耗时：每月 3-5 小时

✅ Power Query 方式

第一次：设置数据源（文件夹）和清洗步骤（约 30-60 分钟）

每月更新：把新文件放入文件夹，点"全部刷新"

等待 10-30 秒，清洗好的数据自动更新

去喝咖啡

**第一次设置后：每月

Power Query 的适用场景

• **多文件合并：**把一个文件夹里的多个 Excel/CSV 文件合并成一张表
• **格式清洗：**删除空行、去除单位字符、拆分合并单元格内容、统一日期格式
• **数据关联：**相当于强化版的 VLOOKUP，把多张表关联合并（不怕大数据量）
• **定期更新：**数据源定期更新，只需一键刷新，无需重新操作

ℹ️ Note

**入口：**数据（Data）选项卡 → 获取数据（Get Data）→ 各种数据源。或直接点"从表格/区域"把当前工作表数据导入 Power Query 编辑器。

Power Query 入门

支持的数据来源

• **Excel 文件：**数据 → 获取数据 → 来自文件 → 来自工作簿
• **CSV/TXT 文件：**数据 → 获取数据 → 来自文件 → 来自文本/CSV
• **文件夹（批量合并）：**数据 → 获取数据 → 来自文件 → 来自文件夹（本章重点）
• **数据库：**支持 SQL Server、MySQL、PostgreSQL 等
• **网页：**可以直接从网页抓取表格数据

Power Query 编辑器界面

打开 Power Query 编辑器后，你会看到：

• 左侧：查询列表（Queries）——所有已建立的查询，可以有多个
• 中间：数据预览区——实时预览当前查询步骤后的数据状态
• 右侧：查询设置（Query Settings）——最重要的是"应用的步骤"（Applied Steps），记录了所有操作
• 顶部：功能区——各种数据转换操作按钮

✅ Tip

**核心机制：每一步操作都会被记录为一个"步骤"，**可以随时查看、删除、修改某一步，也可以在两步之间插入新步骤。这使得 Power Query 的操作高度可逆，不用担心操作失误。

一键刷新：数据更新后的正确姿势

Power Query 最终把处理好的数据输出到 Excel 工作表。当数据源更新后：

• **单个查询刷新：**右键工作表中的查询结果表 → 刷新
• **全部刷新：**数据选项卡 → 全部刷新（Refresh All）——所有查询一次刷新
• **自动刷新：**可设置打开文件时自动刷新，或按时间间隔自动刷新

数据清洗操作（含 AI 辅助）

Power Query 提供了丰富的数据清洗功能，全部通过界面操作完成，无需手写公式。以下是6个最常用的核心操作。

案例1：删除重复行（一键去重）

❌ 旧做法：手动排序后逐行检查，或用 COUNTIF 标记再筛选删除 ✅ Power Query 做法：

1
在 Power Query 编辑器中，选中需要去重的列（或不选任何列则按全行去重）


2
主页（Home）选项卡 → 删除行（Remove Rows）→ 删除重复项（Remove Duplicates）

效果：所有完全重复的行只保留第一次出现的，后续重复行全部删除。
    选中特定列后操作：按选中列的值判断重复（其他列不同但关键列相同也算重复）。
    这个操作在"应用的步骤"里记录为一步，可随时删除撤销。

**节省时间：**10000行数据手工去重 ≈ 30分钟；Power Query 去重 ≈ 5秒。下次刷新自动处理新数据中的重复行。

案例2：拆分列（姓名+手机号合并列拆开）

❌ 旧做法：用 LEFT/RIGHT/MID/FIND 组合公式逐一提取，写一长串公式 ✅ Power Query 做法：几乎不需要写公式

1
选中要拆分的列（如"张三-13812345678"这种格式的联系人列）


2
转换（Transform）选项卡 → 拆分列（Split Column）→ 按分隔符（By Delimiter）


3
输入分隔符（"-"），选择"每次出现分隔符时"或"最左/最右侧的分隔符"

支持多种拆分方式：
    - 按分隔符（逗号、空格、横线等）
    - 按字符数（固定长度拆分）
    - 按大小写转换位置
    - 按数字与字母的边界
    效果：一列变多列，原始数据不受影响（Power Query 是非破坏性处理）。

**节省时间：**写 FIND+MID 公式且测试正确 ≈ 15-30分钟；Power Query 拆分 ≈ 30秒。且界面操作容易理解，不需要公式知识。

案例3：填充空值（向下填充合并单元格）

❌ 旧做法：取消合并单元格后，手动选中空白区域，Ctrl+D 填充，繁琐且容易出错 场景：从系统导出的报表，部门列是合并单元格，一个部门只有第一行有值，后面的行是空的。

1
在 Power Query 编辑器中，右键点击部门列


2
填充（Fill）→ 向下（Down）

效果：每个空值自动填入其上方最近的非空值，等效于"取消合并并填充"。
    从 ERP/系统导出的数据经常有这个问题，Power Query 一步解决。
    同理：向上填充（Fill Up）——空值填入其下方最近的非空值。

**节省时间：**手工处理100行合并单元格数据 ≈ 5-10分钟；Power Query ≈ 3秒。且可重复使用，下次新数据自动处理。

案例4：替换和清洗（去掉"元"/"万"等单位）

❌ 旧做法：SUBSTITUTE 函数去掉单位，再 VALUE() 转数字，两步操作还可能忘记 场景：从财务系统导出的金额列，数据是"125,000元"格式，需要变成纯数字 125000。

1
选中金额列 → 转换 → 替换值（Replace Values）


2
查找"元"，替换为空（什么都不填）→ 确定


3
再次替换：查找","，替换为空（去掉千位分隔符）


4
右键列名 → 更改类型 → 小数或整数（自动识别为数字）

每个替换操作都是单独一步，可以随时删除或调整顺序。
    还可以：主页 → 转换 → 格式 → 修整（Trim，去掉首尾空格）、清理（Clean，去掉不可见字符）。
    批量清洗规则：替换多个不同字符时，逐一添加替换步骤，步骤会依次执行。

**节省时间：**对500行数据用公式清洗 ≈ 20分钟（写公式+验证+转化为值）；Power Query ≈ 1分钟，下次刷新自动处理。

案例5：数据类型转换（文本→日期→数字）

❌ 旧做法：DATEVALUE()、VALUE() 函数转换，格式不对还要用 TEXT() 调整，多步骤容易出错

1
点击列标题左侧的数据类型图标（显示当前类型，如 ABC 表示文本）


2
从弹出的类型菜单选择目标类型（日期、整数、小数、文本等）

Power Query 对数据类型非常严格，建议第一步就确认每列的类型是否正确。
    常见类型问题及处理：
    - "20260425" 文本格式的日期 → 整数 → 自定义格式 → 或用 M 语言 Date.FromText()
    - "1,234.56" 带千位符的文本数字 → 先替换逗号 → 再转小数
    - Excel 日期序列数（如 46000）→ 直接选日期类型即可识别

案例6：条件列（相当于新增 IF 判断列）

❌ 旧做法：在 Excel 里写 =IF(E2>10000,"大客户",IF(E2>5000,"中客户","小客户"))，公式铺满一列 ✅ Power Query 做法：用界面操作实现 IF/SWITCH 逻辑，无需写公式

1
添加列（Add Column）选项卡 → 条件列（Conditional Column）


2
设置列名（如"客户等级"），添加多个 IF/ELSE IF 条件和对应输出值


3
Order Amount 大于 10000 → 输出"大客户"；大于 5000 → "中客户"；否则 → "小客户"

效果：新增一列，根据条件自动填入对应值，等同于嵌套 IF 函数，但无需手写公式。
    条件可以是：等于/不等于/大于/小于/包含/以...开头等多种判断方式。

**节省时间：**写嵌套 IF 公式且调试正确 ≈ 10-20分钟；Power Query 条件列 ≈ 2分钟，且可视化配置，逻辑一目了然。

AI 案例：把杂乱数据截图给 AI，让 AI 写清洗步骤

我有一份从 ERP 系统导出的采购订单 CSV，数据存在以下问题：
1. 金额列格式是 "¥12,345.00元"，需要变成纯数字 12345
2. 供应商列有些行是空的，应该填入同一订单前一行的供应商名（合并单元格导出问题）
3. 日期列格式是 "2026年04月25日"，需要转成 Excel 能识别的标准日期格式
4. 有一列"备注"经常有换行符（从系统里带过来的），需要替换成空格
5. 有重复的订单行（订单号相同），只保留金额最大的那条

请给出在 Power Query 中处理这5个问题的具体步骤，按顺序列出，说明每步用哪个菜单操作或需要用 M 语言代码。

多表合并：Power Query 最强用法

数据清洗只是 Power Query 的入门用法，它真正让人折服的强项是多表合并——把分散在多个文件或多张表里的数据整合到一起。

追加查询（Append）：多个格式相同的表竖向合并

场景：1月、2月、3月……12月各一个 Excel 文件，格式完全相同，需要合并成一张全年数据表。

1 分别导入每个月的查询（或用文件夹方式批量导入，见下文）

2 主页 → 追加查询（Append Queries）→ 三个或更多表

3 按顺序选择要合并的查询，确定

效果：所有月份数据竖向叠加，按行合并为一张大表。
  列名不一致时：Power Query 会按列名对齐，找不到匹配列名的会显示 null，需要先统一各表的列名。

合并查询（Merge）：相当于 VLOOKUP，横向关联

场景：订单表里有产品编号，产品信息表里有产品名称和价格，需要把产品信息带入订单表。

1 在订单查询里：主页 → 合并查询（Merge Queries）

2 右侧选择产品信息查询，选择两张表的关联列（订单表的产品编号 = 产品表的产品编号）

3 联接类型选"左外部"（保留订单表所有行，匹配产品信息）

4 展开新增列，选择要带入的字段（产品名称、单价等）

联接类型说明（类似 SQL JOIN）：
  左外部：保留左表所有行，右表无匹配则填 null（最常用，等同于 VLOOKUP）
  内部：只保留两表都能匹配的行
  完全外部：保留两表所有行，无匹配填 null
  优点 vs VLOOKUP：不怕大数据量、支持多列关联、不存在列位置变化的问题。

文件夹批量合并：一个文件夹里12个月度文件→一张汇总表

这是 Power Query 最令人惊喜的功能。把所有月份文件放在一个文件夹里，Power Query 自动读取所有文件并合并。

1 数据 → 获取数据 → 来自文件 → 来自文件夹 → 选择存放月度文件的文件夹

2 Power Query 显示文件夹内所有文件列表，点"合并并转换数据"

3 选择示例文件（Power Query 用这个文件学习数据结构），选择要读取的工作表

4 Power Query 自动为每个文件应用相同的处理逻辑，合并所有数据，并新增"Source.Name"列（标注来源文件名）

5 在编辑器里继续做清洗（删除多余列、统一格式等），然后关闭并上载

✅ Tip

**下月操作：**把新的月度文件复制到那个文件夹，回到 Excel 点"全部刷新"，新月份数据自动并入汇总表。整个操作不超过30秒。这就是 Power Query 最核心的价值主张。

案例1：合并12个月销售文件

场景：全年12个月的销售数据分布在12个 Excel 文件，格式相同

1新建文件夹，把12个月的文件放入 2Power Query 文件夹合并（见上方步骤） 3从 Source.Name 列提取月份（如文件名包含"2026-01"，用文本拆分提取"01"） 4删除多余的标题行（文件夹合并后每个文件的标题行也会并入，需要筛选去除） 5刷新验证，关闭并上载到工作表

**节省时间：**手工合并12个文件 ≈ 2小时；Power Query 设置 ≈ 30分钟，之后每月刷新 ≈ 30秒。一年下来节省约 22 小时。

案例2：订单表关联产品信息表（替代 VLOOKUP）

场景：10万行订单数据，需要关联产品库（500个产品的名称/类别/成本价），VLOOKUP 已经很慢

1分别导入订单表和产品库为两个 Power Query 查询 2在订单查询中：合并查询 → 选择产品库 → 关联字段选"产品编号" → 左外部联接 3展开合并列，选择需要的字段（产品名称、类别、成本价）

**优势对比 VLOOKUP：**10万行数据，VLOOKUP 可能需要几分钟计算；Power Query 合并查询速度更快，而且结果是静态值，不会因每次打开重新计算。

案例3：多店铺数据汇总

场景：连锁品牌，10家门店每日发来销售日报（格式相同），需要汇总 每天把各店铺日报复制到同一个文件夹，Power Query 文件夹合并自动汇总所有门店数据，加上 Source.Name 列后可按门店分析。

**核心价值：**从"收到10份报告手工合并"变成"收到报告放入文件夹，点刷新"。管理10家门店的数据汇总从每天1小时 → 每天1分钟。

案例4：每周自动刷新最新数据

场景：数据分析报告每周更新，数据源是一个共享网络驱动器上的文件

1Power Query 连接网络路径的数据源文件 2在连接属性（右键查询 → 属性）中设置：刷新频率60分钟 + 打开文件时自动刷新 3每次打开汇总分析文件，数据自动从数据源拉取最新内容

案例5：AI 帮你设计合并逻辑

我需要在 Excel 中用 Power Query 整合以下数据，请帮我设计合并策略：

数据源1：销售明细表（每月一个文件，共12个，格式相同）
- 字段：订单号、日期、产品编号、数量、单价、客户编号、销售员编号

数据源2：产品信息表（一个文件，约500行）
- 字段：产品编号、产品名称、产品类别、成本价、供应商

数据源3：客户信息表（一个文件，约1000行）
- 字段：客户编号、客户名称、所在城市、客户等级

数据源4：员工表（一个文件，约50行）
- 字段：销售员编号、姓名、所属区域、入职日期

最终目标：一张包含所有维度的宽表，用于做全年销售分析（按产品/客户/区域/销售员分析）。

请给出：1）建议的合并顺序和方式；2）关键步骤说明；3）可能遇到的问题和预防措施。

M 语言入门

Power Query 的每一个界面操作，背后都会生成对应的 M 语言代码。M 语言（也叫 Power Query Formula Language）是 Power Query 的编程语言。

你可以在 Power Query 编辑器中点击"高级编辑器"（View → Advanced Editor）查看当前查询的完整 M 代码。

你不需要手写 M 语言，但需要知道它的存在

对大多数用户来说，界面操作就够了。但有些复杂需求只能用 M 语言实现（如动态日期范围、复杂的文本解析、条件分组等）。这时候，最好的方式是让 AI 帮你生成。

AI 生成 M 语言案例一：提取文件名中的日期

我在 Power Query 中合并了一个文件夹的销售文件，Source.Name 列包含文件名，格式是"2026-03-销售数据.xlsx"。我需要新增一列"年月"，提取文件名中的"2026-03"部分。请给我 Power Query 的 M 语言代码（添加自定义列的公式），并解释每个函数的作用。

AI 生成 M 语言案例二：动态日期过滤

在 Power Query 中，我想在日期过滤步骤里，每次刷新自动只保留距今30天内的数据（而不是固定日期）。界面操作的过滤器无法实现动态日期，请给我 M 语言的自定义过滤代码。

AI 生成 M 语言案例三：处理复杂的数据格式

我的数据有一列日期，格式是"Apr 25, 2026"（英文月份缩写+日+年），Power Query 无法自动识别为日期类型。请给我 M 语言代码把这种格式转换成标准日期。

综合案例：全年销售数据自动汇总系统

把本章所有内容整合成一个完整的自动化数据汇总系统。

系统设计目标

输入：每月销售部门发来的 Excel 文件（格式固定，放入指定文件夹）

输出：自动更新的全年销售汇总表，支持按产品/区域/销售员/月份分析

维护成本：每月将新文件复制到文件夹，点一次刷新按钮

实现步骤

1 建立查询1：销售明细

来自文件夹 → 合并12个月文件 → 清洗（删重复/统一格式/填充空值/提取月份） → 命名为"销售明细"

2 建立查询2：产品信息

来自产品信息 Excel → 只选取需要的列 → 命名为"产品信息"（设为仅连接，不输出到工作表）

3 建立查询3：客户信息

同上 → 命名为"客户信息"（仅连接）

4 建立查询4：汇总宽表

复制销售明细查询 → 依次合并产品信息（关联产品编号）和客户信息（关联客户编号）→ 展开需要的字段 → 命名为"销售汇总"→ 关闭并上载到新工作表

5 在汇总表上建立数据透视表

以"销售汇总"查询结果为数据源 → 建立数据透视表 → 每次刷新 Power Query 后再刷新数据透视表

✅ Tip

**第一次建立约需：**2-4 小时（含测试和调整）

**后续每月维护：**约 2 分钟（放文件 + 点刷新 + 等待）

**一年节省时间：**保守估计 20-40 小时

Power Query 速查表

上一章 ← 第9章：条件格式 下一章 第11章：多表操作 →