如何在Power Query编辑器中计算质量数据的一次合格率？

需求说明 #

需基于实验室测试数据计算一次合格率，公式为：
一次合格率 = 首次测试即合格的批次总数 / 总批次数量
核心规则：

• Batch_id对应唯一生产批次，可能存在多次测试记录（首次不合格需返修重测）
• 仅以每个批次首次测试的Pass/Fail结果作为判断依据

样本数据 #

Date          Time        Batch_id      Pass/Fail
4/20/2023   10:00 AM        100         Yes
4/20/2023   11:00 AM        101         No
4/20/2023   11:15 AM        101         Yes
4/20/2023   11:35 AM        102         No
4/20/2023   11:55 AM        102         No
4/20/2023   12:30 PM        102         Yes
4/20/2023   12:50 PM        103         Yes
4/20/2023   1:30  PM        104         Yes
4/20/2023   1:45 PM         105         No
4/20/2023   1:55 PM         105         No
4/20/2023   2:15 PM         105         Yes
4/20/2023   2:35 PM         106         Yes
4/20/2023   2:55 PM         107         No
4/20/2023   3:15 PM         107         Yes
4/20/2023   3:35 PM         108         Yes
4/20/2023   3:45 PM         109         No
4/20/2023   4:30 PM         109         Yes
4/20/2023   4:50 PM         110         Yes
4/20/2023   5:30 PM         111         No
4/20/2023   5:55 PM         111         No
4/20/2023   6:30 PM         112         Yes

实现步骤（界面操作 + M代码） #

步骤1：导入数据到Power Query #

Excel中通过「数据」选项卡→「从表格/范围」将数据导入Power Query编辑器。

步骤2：提取每个批次的首次测试记录 #

1. 选中Batch_id列，点击「转换」选项卡→「分组依据」
2. 分组窗口设置：
   • 分组依据：Batch_id
   • 新列名：首次测试记录
   • 操作：所有行
3. 点击「添加列」→「自定义列」，输入公式提取每组第一条记录：

   Table.First([首次测试记录])
   

4. 展开自定义列，仅保留Pass/Fail字段（其他字段可按需选择）

步骤3：统计合格批次与总批次 #

1. 点击「分组依据」→选择「高级」选项：
   • 第一个分组：
     • 新列名：总批次
     • 操作：计数行
     • 列：Batch_id
   • 第二个分组：
     • 新列名：首次合格批次
     • 操作：行计数
     • 列：Pass/Fail
     • 条件：等于 "Yes"
2. 点击确定后，即可得到总批次和首次合格批次的数量

步骤4：计算一次合格率 #

添加自定义列，输入公式计算合格率：

[首次合格批次] / [总批次]

右键该列→「更改类型」→「百分比」，即可得到百分比格式的结果

完整M代码参考 #

若直接编写M代码，可替换导入后的默认代码（需将Table1替换为你的数据源表名）：

let
    源 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    按批次分组 = Table.Group(源, {"Batch_id"}, {{"首次测试记录", each _, type table [Date=text, Time=text, Batch_id=number, Pass/Fail=text]}}),
    提取首次测试结果 = Table.AddColumn(按批次分组, "首次测试结果", each Table.First([首次测试记录])[Pass/Fail]),
    统计核心数据 = Table.Group(提取首次测试结果, {}, {
        {"总批次", each Table.RowCount(_), Int64.Type},
        {"首次合格批次", each List.Count(List.Select([首次测试结果], (x) => x = "Yes")), Int64.Type}
    }),
    计算合格率 = Table.AddColumn(统计核心数据, "一次合格率", each [首次合格批次]/[总批次], type number),
    设置百分比格式 = Table.TransformColumns(计算合格率, {{"一次合格率", Percentage.From, type percentage}})
in
    设置百分比格式

结果验证 #

样本数据中总批次为13，首次合格批次为7，最终一次合格率为≈53.85%，与代码计算结果一致。

内容的提问来源于stack exchange，提问作者Suri Rathore