一、什么是 Power Query？

Power Query 是 Excel 内置的数据连接和转换工具，它可以从各种数据源（Excel、CSV、数据库、网页、文件夹等）获取数据，然后通过可视化的界面进行清洗、转换和合并，最后将处理好的数据加载到 Excel 工作表中。在 Excel 2016 及以上版本中，它已直接集成在 Excel 中，你可以在“数据”选项卡下找到它——微软官方称它为 “获取和转换数据” 。

一句话理解：Power Query 就像一个“数据加工厂”——原始数据进去，经过一系列标准化处理，最终产出可以直接用于分析的高质量数据。而最关键的是：这个加工流程一旦设定好，就可以反复使用，新增数据时只需点击刷新，整个过程自动完成。

二、为什么要用 Power Query？

核心优势

① 可视化操作，无需写代码

所有数据清洗操作（删除空行、更改数据类型、拆分列、合并表等）都可以通过点击菜单完成，实时预览处理结果，比写公式更直观。

② 步骤自动记录，可重复使用

在 Power Query 编辑器中，每一步操作都会被记录在右侧的“查询设置”中。当源数据更新后，只需点击“刷新”，Power Query 就会自动重放这些步骤，生成最新结果。

③ 支持多种数据源

除了 Excel 文件，Power Query 还可以连接 CSV、XML、JSON、SQL Server、Access、Web API 等多种数据源，实现跨源数据整合。

④ 处理能力远超 Excel 工作表

单张工作表最多 104 万行，而 Power Query 可以处理数千万行级别的数据（在 Power BI 中甚至支持更大规模），适合大数据量的预处理。

三、Power Query 的核心操作流程

Power Query 的工作流程可以概括为四个步骤：

连接 → 清洗 → 转换 → 加载

• 连接：从各种数据源导入原始数据

• 清洗：处理缺失值、删除重复项、修正数据类型等

• 转换：拆分/合并列、透视/逆透视、添加自定义列等

• 加载：将处理好的数据加载到 Excel 工作表或数据模型中

整个流程的核心理念是 ETL（提取、转换、加载）。下面我们通过具体案例来逐个击破。

四、Power Query 界面与基础操作

如何进入 Power Query 编辑器？

假设你有一个 Excel 表格（选中任意数据区域）：

1. 选中数据区域（确保有标题行）

2. 数据 → 来自表格/区域（或“从表/范围获取数据”）

3. 在弹出的对话框中确认数据范围，点击 确定

4. 进入 Power Query 编辑器

编辑器布局

进入编辑器后，界面主要分为以下几个区域：

• 功能区：顶部菜单栏，包含“主页”、“转换”、“添加列”等选项卡

• 查询区：左侧列表，显示当前所有查询（可理解为一个数据表）

• 编辑区：中央主区域，显示数据预览

• 查询设置：右侧窗口，记录每一步操作，可以随时删除或调整顺序

• 编辑栏：功能区下方，显示当前步骤的 M 语言公式（可供进阶用户修改）

五、数据清洗实战案例

下面通过一个真实的销售数据案例，逐步演示 Power Query 的核心清洗操作。

初始数据（存在各种问题）

问题清单：

• 产品代码存在空值（第6行）

• 销售额含有千位分隔符（文本格式）

• 产品名称有重复（手机出现两次）

• 销售日期格式不统一（部分为文本）

• 包含不需要的“备注”列

逐步清洗

① 删除空行

选中“产品代码”列 → 主页 → 删除行 → 删除空行。第6行被删除。

② 删除重复项

选中“产品名称”列 → 主页 → 删除行 → 删除重复项。重复的“手机”记录被删除。

③ 更改数据类型

选中“销售额”列 → 主页 → 数据类型 → 选择 小数 或 整数。Power Query 会自动识别并去除千位分隔符。

选中“销售日期”列 → 数据类型 → 选择 日期。

④ 删除不需要的列

选中“备注”列 → 主页 → 删除列（或右键 → 删除）。

⑤ 统一文本格式

选中“产品名称”列 → 转换 → 格式 → 首字母大写。

清洗后结果（干净、整洁、格式统一）：

六、数据合并：追加查询 vs 合并查询

当数据分散在多个表或多个文件中时，Power Query 提供了两种合并方式。

① 追加查询（纵向合并）

适用场景：将结构相同的多个表按行堆叠。例如：将 1 月、2 月、3 月的销售数据表合并成一个季度总表。

操作步骤（以合并同一工作簿内的多个工作表为例）：

1. 数据 → 获取数据 → 从文件 → 从工作簿，选择目标文件并导入。

2. 在导航器中勾选需要合并的所有工作表（可多选），点击 转换数据。

3. 找到名为 Content 或 Data 的列，点击标题右侧的 展开 按钮（双向箭头图标）。

4. 取消勾选“使用原始列名作为前缀”，点击 确定。

5. 删除多余的列（如 Content、Name 等）。

6. 主页 → 将第一行用作标题（如果数据有标题行）。

7. 点击 关闭并加载，合并后的数据将加载到新工作表。

合并多个工作簿（文件夹中的所有 Excel 文件）：

1. 数据 → 获取数据 → 从文件 → 从文件夹，选择文件夹路径，点击 转换数据。

2. 删除除 Content 和 Name 外的其他列。

3. 添加列 → 自定义列，输入公式 =Excel.Workbook([Content])。

4. 展开新列，只勾选 Data 选项。

5. 再次展开 Data 列，取消“使用原始列名作为前缀”。

6. 删除多余列，将第一行用作标题。

7. 关闭并加载。

效果：无论文件夹中有多少文件、每个文件中有多少工作表，只要数据结构相同，Power Query 会自动将它们全部合并。

② 合并查询（横向合并）

适用场景：根据共同字段将两个表按列拼接，类似于 SQL 中的 JOIN 操作。例如：将“订单表”和“客户表”根据客户 ID 合并在一起，获取每个订单的客户姓名。

操作步骤：

1. 将两个表分别加载到 Power Query 编辑器。

2. 选中其中一个表，主页 → 合并查询 → 合并查询（或“合并查询为新查询”）。

3. 在弹出的对话框中：

• 选择第一个表（左表）和连接列（如“客户 ID”）

• 选择第二个表（右表）和对应的连接列

4. 选择合适的联接类型：

1. 点击 确定。右表会作为一列出现在左表中。

2. 点击该列标题右侧的 展开 按钮，勾选需要保留的字段，取消勾选“使用原始列名作为前缀”，点击 确定。

效果：合并后的表包含两个表中的相关列，实现横向数据整合。

七、数据转换进阶操作

① 拆分列

将一列按分隔符拆分成多列。例如：将“张三-销售部”拆分为“姓名”和“部门”。

操作：选中目标列 → 转换 → 拆分列 → 按分隔符 → 选择分隔符（如 -）。

② 透视列与逆透视列

• 透视（Pivot）：将行数据转为列（类似数据透视表）。例如：将“月份”行的值变成列标题。

• 逆透视（Unpivot）：将列数据转回行（将宽表变成长表），这是 Power Query 的一大特色功能。例如：将“1月、2月、3月”等多列销售额合并成“月份”和“销售额”两列。

③ 添加条件列（类似 IF 函数）

需求：根据销售额添加“达标”标识（≥10000 为“达标”，否则为“未达标”）。

操作：添加列 → 条件列 → 设置条件和输出值。

④ 分组依据（类似 SUMIF）

对数据进行分组并计算聚合值（求和、计数、平均值等）。例如：按“产品名称”分组，计算总销售额。

操作：转换 → 分组依据 → 选择分组列和聚合方式。

八、数据的刷新与自动化

Power Query 的核心优势在于自动化。

• 刷新当前查询：右键查询结果表 → 刷新。

• 刷新所有查询：数据 → 全部刷新（快捷键 Alt + F5）。

• 设置自动刷新：数据 → 查询和连接 → 右键查询 → 属性 → 设置刷新间隔（需要保持工作簿打开状态）。

九、常见问题与解决方法

十、Power Query vs VBA：如何选择？

建议：

• 优先使用 Power Query 处理数据清洗、多文件合并、定期更新等任务。

• VBA 适用于需要自定义交互界面、复杂循环逻辑、操作工作簿结构等 Power Query 无法直接完成的场景。

恭喜你又学到最后咯，加油我和大家在一起学习的路上。