Power Query数据处理

本章概览

Power Query是Excel中强大的数据提取、转换、加载(ETL)工具。它能够从多种数据源获取数据,进行复杂的数据清洗和转换,且所有操作步骤可记录、可重复执行。

学习目标

• 掌握Power Query界面和基本操作
• 学会从多种数据源导入数据
• 熟练进行数据转换和清洗
• 掌握表合并和追加技巧
• 学会M语言基础和自定义函数

---

26.1 Power Query入门

26.1.1 启动Power Query

导入数据

数据 → 获取数据 →
- 从文件(Excel/CSV/文本/XML/JSON)
- 从数据库(SQL Server/Access/Oracle)
- 从其他源(Web/文件夹/SharePoint)
- 从表/范围

Power Query编辑器

组成部分:
1. 菜单栏:主页/转换/添加列/查看
2. 查询窗格:左侧,显示所有查询
3. 预览窗格:中间,显示数据预览
4. 查询设置:右侧,显示应用的步骤
5. 公式栏:显示当前步骤的M代码

26.1.2 基本工作流程

ETL流程

Extract (提取) → Transform (转换) → Load (加载)

示例流程

1. 获取数据:导入Excel文件
2. 转换数据:
   - 删除不需要的列
   - 筛选行
   - 更改数据类型
   - 拆分列
   - 合并列
3. 加载数据:关闭并上载到工作表

应用的步骤

每个操作都会在"应用的步骤"中记录:
□ 源
□ 导航
□ 更改的类型
□ 删除的列
□ 筛选的行
□ 拆分的列

可以:
- 删除步骤
- 编辑步骤
- 重新排序
- 插入新步骤

26.1.3 刷新查询

手动刷新

数据 → 全部刷新
或右键查询 → 刷新

作用:重新执行所有步骤,获取最新数据

自动刷新

数据 → 查询和连接 → 查询属性 → 刷新控制
勾选"打开文件时刷新数据"

---

26.2 数据源连接

26.2.1 从Excel导入

从工作簿导入

数据 → 获取数据 → 从文件 → 从工作簿
1. 选择Excel文件
2. 导航器窗口选择工作表或表
3. 转换数据进入编辑器

技巧:
- 勾选"表格或范围包含标题"
- 可同时选择多个工作表

从文件夹导入(批量)

数据 → 获取数据 → 从文件 → 从文件夹
1. 选择包含多个Excel的文件夹
2. 合并文件
3. 自动合并所有工作簿

应用:
批量处理月度报表、分公司数据等

26.2.2 从CSV/文本导入

从CSV

数据 → 从文本/CSV
自动检测:
- 分隔符(逗号/分号/Tab)
- 编码(UTF-8/GB2312)
- 数据类型

手动调整:
- 转换数据 → 编辑器手动调整

处理乱码

文件原点下拉框选择:
- 简体中文(GB2312)
- Unicode(UTF-8)
- 繁体中文(Big5)

26.2.3 从Web导入

从网页导入表格

数据 → 获取数据 → 从其他源 → 从Web
1. 输入URL
2. Power Query识别网页中的表格
3. 选择需要的表格导入

示例:
导入汇率数据、股票行情、天气数据等

定期刷新

设置自动刷新
→ 每次打开文件获取最新网页数据

26.2.4 从数据库导入

SQL Server

数据 → 获取数据 → 从数据库 → 从SQL Server
1. 服务器名称
2. 数据库名称(可选)
3. SQL语句(高级选项)
4. 选择表或视图

示例SQL

SELECT 产品, SUM(销售额) as 总额
FROM 销售表
WHERE 日期 >= '2024-01-01'
GROUP BY 产品

---

26.3 数据转换操作

26.3.1 列操作

删除列

选中列 → 主页 → 删除列
或右键 → 删除

快捷方式:
删除其他列:保留选中的列,删除其他
删除重复项:删除重复的行

重命名列

双击列标题
或右键 → 重命名

更改数据类型

点击列标题左侧图标:
- ABC (文本)
- 123 (整数)
- 1.2 (小数)
- 日历图标(日期)
- 时钟图标(时间)
- ✓× (True/False)

拆分列

选中列 → 转换 → 拆分列
- 按分隔符:逗号/空格/自定义
- 按字符数:固定宽度
- 按大小写:小写到大写
- 按非数字到数字

示例:
"张三,男,28"
→ 按逗号拆分
→ "张三" | "男" | "28"

合并列

选中多列 → 转换 → 合并列
- 选择分隔符
- 新列名称

示例:
省 | 市 | 区
→ 合并列(分隔符:空格)
→ 地址

26.3.2 行操作

删除行

主页 → 删除行
- 删除重复行
- 删除空行
- 删除错误
- 删除其他行

选项:
删除顶部行:删除前N行
删除底部行:删除后N行
删除备用行:隔行删除

保留行

主页 → 保留行
- 保留顶部行:前N行
- 保留底部行:后N行
- 保留范围:第M到N行

筛选行

点击列标题右侧下拉箭头
- 文本筛选:包含/开头是/结尾是
- 数字筛选:大于/小于/介于
- 日期筛选:之前/之后/介于

多条件筛选:
同时对多列设置筛选条件

26.3.3 填充和替换

向下填充

选中列 → 转换 → 填充 → 向下
用途:填充空白单元格

示例:
日期
2024-01-01
(空)
(空)
2024-01-02

→ 填充后
2024-01-01
2024-01-01
2024-01-01
2024-01-02

替换值

选中列 → 转换 → 替换值
要查找的值:旧值
替换为:新值

示例:
将"北京市"替换为"北京"

条件替换

添加列 → 条件列
IF 列名 = 值1 THEN 结果1
ELSE IF 列名 = 值2 THEN 结果2
ELSE 结果3

26.3.4 数据透视和逆透视

逆透视列

应用场景:宽表转长表

原始数据(宽表):
产品    一月  二月  三月
苹果    100   120   110
香蕉     80    90   100

操作:
选中"一月/二月/三月" → 转换 → 逆透视列

结果(长表):
产品    月份    销量
苹果    一月    100
苹果    二月    120
苹果    三月    110
香蕉    一月     80
香蕉    二月     90
香蕉    三月    100

数据透视列

应用场景:长表转宽表(逆操作)

转换 → 透视列
- 值列:销量
- 高级选项:聚合函数(求和/计数/平均值)

---

26.4 合并和追加查询

26.4.1 追加查询(纵向合并)

应用场景 合并结构相同的多个表

操作步骤

主页 → 追加查询
1. 选择"追加查询为新查询"
2. 选择要合并的表
3. 确定

示例:
合并1月、2月、3月销售数据

批量追加

从文件夹导入 → 自动追加
适用:多个Excel文件结构相同

步骤:
1. 获取数据 → 从文件夹
2. 合并并转换数据
3. Power Query自动识别结构并追加

26.4.2 合并查询(横向合并)

类似VLOOKUP

主页 → 合并查询

合并类型:
1. 左外部:保留左表所有行
2. 右外部:保留右表所有行
3. 完全外部:保留两表所有行
4. 内部:只保留匹配的行
5. 左反:左表独有的行
6. 右反:右表独有的行

示例

表1:订单表
订单号  客户ID  金额
001    C001   1000
002    C002   2000

表2:客户表
客户ID  客户名
C001   张三
C002   李四

操作:
1. 选中订单表
2. 主页 → 合并查询
3. 选择客户表
4. 匹配列:客户ID = 客户ID
5. 连接类型:左外部
6. 展开客户表,选择"客户名"列

结果:
订单号  客户ID  金额  客户名
001    C001   1000  张三
002    C002   2000  李四

多列匹配

按住Ctrl选择多列
如:匹配条件为"省份+城市"

---

26.5 添加自定义列

26.5.1 条件列

添加条件列

添加列 → 条件列

示例:根据销售额分级
IF [销售额] >= 10000 THEN "A级"
ELSE IF [销售额] >= 5000 THEN "B级"
ELSE "C级"

26.5.2 自定义列(M语言)

基础示例

添加列 → 自定义列

# 计算列
= [单价] * [数量]

# 文本操作
= Text.Upper([姓名])  # 转大写
= Text.Length([地址])  # 文本长度
= Text.Start([编号], 2)  # 前2个字符

# 日期操作
= Date.Year([日期])  # 提取年份
= Date.AddDays([日期], 7)  # 加7天
= Date.From([文本日期])  # 文本转日期

# 数字操作
= Number.Round([金额], 2)  # 四舍五入
= Number.Abs([差额])  # 绝对值

复杂逻辑

= if [销售额] > 10000 and [地区] = "北京"
  then [销售额] * 0.9
  else [销售额]

---

26.6 分组和聚合

26.6.1 分组依据

基本分组

转换 → 分组依据
1. 分组依据:地区
2. 新列名:总销售额
3. 操作:求和
4. 列:销售额

结果:
地区    总销售额
北京    50000
上海    45000
广州    38000

多列分组

分组依据:地区,产品
聚合:销售额求和,订单数计数

结果:
地区  产品  总销售额  订单数
北京  A    20000    50
北京  B    30000    60
上海  A    25000    55

聚合函数

求和/平均值/最小值/最大值
计数/非重复计数
全部行(返回子表)

26.6.2 高级聚合

保留所有行详细信息

聚合:全部行
→ 每组生成一个子表

用途:
分组后仍需访问明细数据

自定义聚合

使用M语言自定义聚合逻辑
示例:计算中位数、众数等

---

26.7 M语言基础

26.7.1 查看M代码

查看步骤M代码

查看 → 高级编辑器
显示完整的M脚本

或点击公式栏查看当前步骤的M代码

示例代码

let
    源 = Excel.Workbook(File.Contents("C:\数据.xlsx")),
    数据表 = 源{[Item="Sheet1",Kind="Sheet"]}[Data],
    更改的类型 = Table.TransformColumnTypes(数据表,{
        {"日期", type date},
        {"销售额", type number}
    }),
    筛选的行 = Table.SelectRows(更改的类型, each [销售额] > 10000)
in
    筛选的行

26.7.2 常用M函数

表函数

Table.SelectRows(表, 条件函数)  # 筛选行
Table.SelectColumns(表, 列列表)  # 选择列
Table.RemoveColumns(表, 列列表)  # 删除列
Table.RenameColumns(表, 重命名列表)  # 重命名列
Table.AddColumn(表, 列名, 计算函数)  # 添加列
Table.Group(表, 分组列, 聚合列表)  # 分组

文本函数

Text.Upper(文本)  # 大写
Text.Lower(文本)  # 小写
Text.Length(文本)  # 长度
Text.Start(文本, 数量)  # 前N个字符
Text.End(文本, 数量)  # 后N个字符
Text.Contains(文本, 子串)  # 包含判断
Text.Replace(文本, 旧, 新)  # 替换

日期函数

Date.Year(日期)  # 年
Date.Month(日期)  # 月
Date.Day(日期)  # 日
Date.AddDays(日期, 天数)  # 加天数
Date.DayOfWeek(日期)  # 星期几
Date.From(文本)  # 文本转日期

数字函数

Number.Round(数字, 小数位)  # 四舍五入
Number.Abs(数字)  # 绝对值
Number.Mod(数字, 除数)  # 取余
Number.IntegerDivide(被除数, 除数)  # 整除

26.7.3 自定义函数

创建函数

# 简单函数
(x) => x * 2

# 多参数函数
(x, y) => x + y

# 复杂逻辑
(金额) =>
    if 金额 > 10000 then
        金额 * 0.9
    else if 金额 > 5000 then
        金额 * 0.95
    else
        金额

应用自定义函数

1. 新建查询 → 空白查询
2. 高级编辑器粘贴函数代码
3. 重命名查询为"折扣计算"
4. 在其他查询中调用:
   添加列 → 调用自定义函数
   选择函数名和参数列

---

26.8 实战案例

案例1:合并多个Excel文件

场景 文件夹中有1月.xlsx、2月.xlsx、3月.xlsx 结构相同,需合并

步骤

1. 获取数据 → 从文件夹
2. 选择文件夹路径
3. 合并并转换数据
4. 选择"Sheet1"
5. Power Query自动合并
6. 关闭并上载

添加文件名列

展开"Source.Name"列
便于区分数据来源

案例2:清洗脏数据

原始数据问题

- 日期格式混乱
- 金额包含"元"字
- 姓名有多余空格
- 部门名称不统一

清洗步骤

1. 日期列:
   更改类型 → 日期

2. 金额列:
   替换值:"元" → ""
   更改类型 → 数字

3. 姓名列:
   转换 → 修剪(去空格)

4. 部门列:
   替换值:
   "销售部" → "销售"
   "销售科" → "销售"
   "Sales" → "销售"

案例3:数据格式转换

宽表转长表

原始(宽表):
产品  Q1   Q2   Q3   Q4
A    100  120  110  150
B     80   90  100  110

步骤:
1. 选中Q1-Q4列
2. 转换 → 逆透视列
3. 重命名"属性"为"季度"
4. 重命名"值"为"销量"

结果(长表):
产品  季度  销量
A    Q1   100
A    Q2   120
...

案例4:主表明细表关联

主表:订单主表

订单号  客户  日期
O001   张三  2024-01-01
O002   李四  2024-01-02

明细表:订单明细

订单号  产品  数量  单价
O001   A     2    100
O001   B     3    80
O002   A     1    100

关联步骤

1. 导入两个表
2. 选中订单主表
3. 合并查询 → 订单明细
4. 匹配列:订单号
5. 连接类型:左外部
6. 展开明细表,选择产品/数量/单价
7. 添加列:金额 = [数量] * [单价]

结果:每个订单展开为多行明细

---

26.9 Power Query最佳实践

26.9.1 性能优化

减少步骤

- 合并多个替换值步骤
- 删除不必要的中间步骤
- 提前筛选行,减少数据量

查询折叠

概念:将Power Query步骤转换为数据源查询
适用:数据库连接

优势:在数据源端处理,提高性能

查看:右键步骤 → 查看本机查询

关闭自动类型检测

文件 → 选项和设置 → 查询选项
数据加载:取消勾选"自动检测列类型和标题"

手动指定类型,避免重复检测

26.9.2 可维护性

命名规范

查询命名:
- 原始数据:Raw_客户数据
- 中间查询:Tmp_清洗后数据
- 最终输出:Out_客户分析表

步骤命名:
- 重命名步骤为有意义的名称
- 如"筛选北京地区"而非"筛选的行"

添加注释

在M代码中添加注释:
// 这是注释
/* 多行
   注释 */

模块化

- 将通用逻辑封装为自定义函数
- 不同数据源分别创建查询
- 最后用合并/追加整合

---

本章小结

核心要点

1. Power Query:ETL工具,可记录、可重复
2. 数据源:Excel/CSV/Web/数据库/文件夹
3. 转换操作:列操作/行操作/填充/替换
4. 合并追加:横向合并(JOIN)/纵向追加(UNION)
5. M语言:Power Query的公式语言
6. 最佳实践:性能优化、命名规范、模块化

Power Query vs 公式

对比项	Power Query	Excel公式
可视化	✓ 界面操作	✗ 代码编写
可重复	✓ 刷新即可	✗ 需重新操作
性能	✓ 批量处理快	✗ 大数据慢
灵活性	✓ 复杂转换	✗ 有限制
学习曲线	中等	较低

适用场景

• 定期更新的数据报表
• 需要复杂清洗的脏数据
• 多个数据源合并
• 批量处理文件
• 数据格式转换(宽窄表)

下一步学习

• 第27章:Power Pivot数据建模
• 第28章:Excel VBA编程基础
• 第29章:Excel与Python集成

---

思考练习

1. 从文件夹批量导入多个月度报表并合并
2. 清洗一份包含多种格式问题的客户数据
3. 将宽表销售数据转换为长表格式
4. 关联订单主表和明细表,计算订单金额
5. 创建一个自定义函数,根据销售额计算提成

练习提示

• 每个步骤都会记录,可以撤销重做
• 多用"应用的步骤"查看和管理
• 善用预览功能检查结果
• 保存查询便于下次使用