当前位置：首页 > news >正文

没想到吧？用Excel+Power Query也能做ETL数据清洗！

news 2026/5/12 18:16:18

被忽视的测试数据困境

作为软件测试从业者，你是否经常面临这样的困境：

需要从生产环境导出的混乱Excel中提取测试用例数据
手动清洗上百条订单状态的测试数据耗费数小时
不同业务系统导出的数据格式不统一，难以直接用于自动化测试
临时需要构造特定边界值的数据组合却无从下手

传统认知中，ETL（提取-转换-加载）是数据工程师的专属领域，需要专业的Datax、Kettle等工具。但事实上，测试工程师每天接触的Excel中，就隐藏着一个强大的ETL工具——Power Query，它能将你从重复繁琐的数据准备工作中解放出来。

一、为什么测试工程师需要掌握ETL思维

1.1 测试数据准备的本质就是ETL过程

测试数据准备本质上就是一个典型的ETL流程：

提取（Extract）：从数据库、日志文件、API接口等来源获取原始数据
转换（Transform）：对数据进行清洗、格式化、脱敏、构造等操作
加载（Load）：将处理后的数据导入测试环境或直接用于测试执行

1.2 测试数据管理的核心痛点

数据一致性：多来源数据的格式统一问题
数据有效性：构造符合业务规则的测试数据
数据可追溯性：维护测试数据与用例的关联关系
数据安全性：生产数据脱敏后的测试使用

二、Power Query在测试数据准备中的实战应用

2.1 环境准备与基础配置

启用Power Query编辑器

Excel 2016及以上版本：数据→获取数据→启动Power Query编辑器
Office 365：功能完整的最新版本Power Query

基础界面认知

左侧导航：查询列表与数据源管理
中部区域：数据预览与即时编辑
右侧面板：应用的步骤记录，支持回滚操作
顶部菜单：数据转换的核心功能区

2.2 典型测试数据清洗场景

场景一：多源测试数据整合

// 合并多个系统的用户数据表 let Source1 = Excel.Workbook(File.Contents("CRM用户数据.xlsx"), null, true), Source2 = Excel.Workbook(File.Contents("ERP用户数据.xlsx"), null, true), 合并数据 = Table.Combine({Source1, Source2}) in 合并数据

测试应用价值：

跨系统业务流程测试的数据准备
性能测试中大规模用户数据的快速构造
兼容性测试的多版本数据格式统一

场景二：测试数据标准化处理

// 统一日期格式并生成测试用例编号 let Source = Excel.CurrentWorkbook(){[Name="原始测试数据"]}[Content], 标准化日期 = Table.TransformColumns(Source,{{"订单日期", each Date.From(_, "zh-CN"), type date}}), 生成用例ID = Table.AddColumn(标准化日期, "测试用例编号", each "TC_" & Text.PadStart(Text.From([序号]), 4, "0")) in 生成用例ID

测试应用价值：

自动化测试脚本的数据格式要求满足
测试用例与测试数据的关联管理
测试执行结果的精准定位

场景三：边界值测试数据自动生成

// 生成金额字段的边界值测试数据 let 基础数据 = #table(type table [订单金额=number], {{0}, {0.01}, {9999.99}, {10000}, {10000.01}}), 添加预期结果 = Table.AddColumn(基础数据, "预期结果", each if [订单金额] < 0 then "失败-金额不能为负" else if [订单金额] > 10000 then "失败-超过单笔限额" else "成功") in 添加预期结果

测试应用价值：

边界值分析法的数据快速构造
等价类划分的测试数据覆盖率保证
异常场景测试的完整数据准备

三、Power Query在测试自动化中的进阶应用

3.1 与测试框架的数据集成

方案一：导出为标准测试数据文件

// 生成JUnit数据驱动测试所需的CSV文件 let Source = //...数据处理逻辑, 导出配置 = Table.TransformColumnTypes(Source,{ {"TestCase", type text}, {"InputData", type text}, {"ExpectedResult", type text} }) in 导出配置

方案二：直接生成测试脚本代码

// 自动生成Python unittest测试数据 let Source = //...测试数据准备, 生成代码 = Table.AddColumn(Source, "TestCode", each "def test_" & [场景编号] & "():" & "#13;" & " input_data = " & [输入数据] & "#13;" & " expected = " & [预期结果] & "#13;" & " self.assertEqual(process(input_data), expected)") in 生成代码

3.2 测试数据质量监控

数据完整性检查

// 检查测试数据集的质量问题 let Source = //...数据源, 空值统计 = Table.AddColumn(Source, "空值数量", each List.NonNullCount(Record.FieldValues(_))), 数据质量标记 = Table.AddColumn(空值统计, "数据质量", each if [空值数量] < 3 then "数据不完整" else "数据完整") in 数据质量标记

业务规则验证

// 验证测试数据是否符合业务规则 let Source = //...测试数据, 规则验证 = Table.AddColumn(Source, "规则检查", each if [年龄] < 18 and [产品类型] = "酒精饮品" then "违反业务规则" else if [信用额度] > [月收入] * 3 then "信用规则异常" else "规则通过") in 规则验证