第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 数据库名称, 连接字 ProviderSQLNCLI11;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, [IgnoreCasetrue, IgnoreSpacetrue] )5. 数据透视与逆透视5.1 透视列将行值转为列透视结果 Table.Pivot( 源, List.Distinct(源[月份]), 月份, 销售额, List.Sum )转换前产品月份销售额A1月100A2月150B1月200转换后产品1月2月A100150B200null5.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(服务器, 数据库){[Schemadbo, 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、验证逻辑性能优化查询折叠、增量刷新下一篇我们将探讨企业级报表开发与最佳实践。