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

第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 )

转换前：

转换后：

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 高级技术：

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