第14篇：Power Query 高级数据处理

1. Power Query 核心概念

1.1 M 语言基础

Power Query 使用 M 语言进行数据转换：

// 基本语法结构
let
    步骤1 = 操作1,
    步骤2 = 操作2,
    结果 = 最终输出
in
    结果

1.2 查询步骤链

源数据
   ↓ 引用类型转换
   ↓ 删除列
   ↓ 筛选行
   ↓ 分组聚合
   ↓ 合并查询
   ↓ 输出结果

---

2. 高级数据获取

2.1 多文件合并

场景： 合并文件夹中的多个 Excel 文件

let
    源 = Folder.Files("C:\Data\Sales"),
    筛选Excel = Table.SelectRows(源, each [Extension] = ".xlsx"),
    添加内容 = Table.AddColumn(筛选Excel, "Content", each Excel.Workbook([Content])),
    展开内容 = Table.ExpandTableColumn(添加内容, "Content", {"Data", "Item"}),
    展开数据 = Table.ExpandTableColumn(展开内容, "Data", {"日期", "产品", "金额"})
in
    展开数据

2.2 动态数据源

使用参数化数据源：

let
    服务器名 = Parameter 服务器名称,
    数据库名 = Parameter 数据库名称,
    连接字 = "Provider=SQLNCLI11;Server=" & 服务器名 & ";Database=" & 数据库名,
    数据 = Sql.Database(服务器名, 数据库名)
in
    数据

2.3 Web API 调用

let
    URL = "https://api.example.com/data",
    请求头 = [#"Authorization" = "Bearer your-token"],
    响应 = Json.Document(Web.Contents(URL, [Headers = 请求头])),
    转表 = Table.FromRecords(响应[data])
in
    转表

---

3. 复杂数据清洗

3.1 文本处理

// 提取数字
提取金额 = Table.AddColumn(源, "金额", each Number.From(Text.Select([描述], {"0".."9", "."})))

// 分割列
分割姓名 = Table.SplitColumn(源, "姓名", Splitter.SplitTextByDelimiter(" "), {"姓", "名"})

// 正则替换（需调用 Python）
// 建议在 Power Query 中用基础函数替代
清理文本 = Table.TransformColumns(源, {"字段", each Text.Clean(Text.Trim(_))})

3.2 日期处理

// 从文本解析日期
解析日期 = Table.TransformColumns(源, {"日期列", each Date.FromText(_, "zh-CN"))

// 提取日期部分
添加年月 = Table.AddColumn(源, "年月", each Date.ToText([日期], "yyyy-MM"))

// 计算工作日
工作日判断 = Table.AddColumn(源, "是否工作日", each List.Contains({1..5}, Date.DayOfWeek([日期], Day.Monday) + 1))

3.3 数据类型推断

// 批量类型转换
类型转换 = Table.TransformColumnTypes(
    源,
    {
        {"日期", type date},
        {"金额", type number},
        {"数量", Int64.Type}
    },
    "zh-CN"
)

---

4. 高级合并与连接

4.1 多表合并

let
    表1 = Excel.Workbook(File.Contents("销售.xlsx")){[Item="销售"]}[Data],
    表2 = Excel.Workbook(File.Contents("订单.xlsx")){[Item="订单"]}[Data],
    合并表 = Table.Combine({表1, 表2})
in
    合并表

4.2 条件合并

// 左反连接（仅左表独有）
左反 = Table.NestedJoin(
    表1, {"ID"},
    表2, {"ID"},
    "合并",
    JoinKind.LeftAnti
)

// 完全外连接
全外 = Table.NestedJoin(
    表1, {"ID"},
    表2, {"ID"},
    "合并",
    JoinKind.FullOuter
)

4.3 模糊匹配

模糊匹配 = Table.FuzzyNestedJoin(
    表1, {"姓名"},
    表2, {"姓名"},
    "匹配结果",
    JoinKind.LeftJoin,
    [IgnoreCase=true, IgnoreSpace=true]
)

---

5. 数据透视与逆透视

5.1 透视列

将行值转为列：

透视结果 = Table.Pivot(
    源,
    List.Distinct(源[月份]),
    "月份",
    "销售额",
    List.Sum
)

转换前：

产品	月份	销售额
A	1月	100
A	2月	150
B	1月	200

转换后：

产品	1月	2月
A	100	150
B	200	null

5.2 逆透视列

将列转为行值：

逆透视 = Table.Unpivot(
    源,
    {"1月", "2月", "3月"},
    "月份",
    "销售额"
)

---

6. 自定义函数

6.1 创建函数

// 函数：计算税后价格
税后价格 = (价格 as number, 税率 as number) as number =>
    let
        结果 = 价格 * (1 + 税率)
    in
        结果

// 使用函数
添加税后 = Table.AddColumn(源, "税后价", each 税后价格([单价], 0.13))

6.2 递归函数

// 计算阶乘
阶乘 = (n as number) as number =>
    if n <= 1 then 1
    else n * @阶乘(n - 1)

6.3 参数化函数

// 动态数据获取
获取数据 = (服务器 as text, 数据库 as text, 表名 as text) =>
    let
        结果 = Sql.Database(服务器, 数据库){[Schema="dbo", Item=表名]}[Data]
    in
        结果

---

7. 高级分组聚合

7.1 多级分组

多级分组 = Table.Group(
    源,
    {"地区", "产品类别"},
    {
        {"销售总额", each List.Sum([销售额])},
        {"订单数量", each Table.RowCount(_)},
        {"平均单价", each List.Average([单价])}
    }
)

7.2 条件分组

条件分组 = Table.Group(
    源,
    {"客户"},
    {
        {"大订单数", each Table.RowCount(Table.SelectRows(_, (r) => r[金额] > 10000))},
        {"小额订单总额", each List.Sum(List.Select([金额], (x) => x <= 1000))}
    }
)

---

8. 错误处理

8.1 Try-Catch

安全转换 = Table.TransformColumns(
    源,
    {"金额", each try Number.From(_) otherwise null}
)

8.2 错误替换

替换错误 = Table.ReplaceErrorValues(
    源,
    {{"金额", 0}, {"日期", null}}
)

8.3 验证逻辑

验证数据 = Table.AddColumn(
    源,
    "验证状态",
    each
        let
            金额检查 = if [金额] >= 0 then "✓" else "✗",
            日期检查 = if [日期] <= Date.From(DateTime.LocalNow()) then "✓" else "✗"
        in
            if 金额检查 = "✓" and 日期检查 = "✓" then "有效" else "无效"
)

---

9. 性能优化

9.1 查询折叠

确保操作可推送至数据源：

支持折叠的操作：
✅ 筛选行
✅ 选择列
✅ 排序
✅ 分组聚合
✅ 合并（同源）

不支持折叠：
❌ 条件列（复杂逻辑）
❌ 自定义函数
❌ 多数据源合并

9.2 禁用加载

查询设置 → 禁用：
✅ 仅作为数据源，不加载到模型

9.3 增量刷新

配置步骤：
1. 设置日期参数
2. 筛选最近数据
3. 发布到服务
4. 配置增量刷新策略

---

10. 最佳实践总结

✅ 使用参数化数据源
✅ 批量处理而非逐行
✅ 尽早筛选减少数据量
✅ 保持查询折叠
✅ 使用函数复用逻辑
✅ 添加描述性步骤名
✅ 处理错误和空值

❌ 避免过度转换
❌ 避免复杂计算列
❌ 避免禁用折叠的操作
❌ 避免引用完整表格
❌ 避免硬编码值

---

11. 小结

本篇介绍了 Power Query 高级技术：

主题	要点
数据获取	多文件合并、动态源、API
数据清洗	文本处理、日期解析、类型转换
合并连接	多表合并、模糊匹配
透视逆透视	列转行、行转列
自定义函数	参数化、递归、复用
错误处理	Try-Catch、验证逻辑
性能优化	查询折叠、增量刷新

下一篇，我们将探讨企业级报表开发与最佳实践。