如何从50万行付款记录中筛选每个protocol的首笔付款

2026-10-24支付小周

结论

不要直接在透视表中按日期筛选，也不要先删除重复项。应先确认哪些记录代表“付款”，把多条 activity 归并到 transaction 层级，然后按 protocol 分组，取最早的付款日期。

建议按以下顺序处理：

筛选付款类 activity。
按 protocol + transaction number 汇总，得到每笔 transaction 的付款日期。
按 protocol 再次分组，取最早日期。
如果还要返回首笔付款对应的 transaction number，需要保留最早日期所在的整行，不能只计算日期最小值。

为什么直接做透视表容易出错

原始数据中，一笔 transaction 可能包含多条 activity，每条 activity 的日期也可能不同。因此，一行数据不一定代表一笔付款。

如果直接对所有 activity 的日期取最小值，结果可能是：

transaction 创建日期；
审批日期；
状态变更日期；
其他早于实际付款的操作日期。

问题不只是“按 protocol 取最小日期”，还要先确定哪类 activity 的日期才是付款日期。假设数据中有 Activity Type 字段，值为 Payment 的记录代表实际付款，可以在 Power Query 中按以下方式处理。实际使用时，需要把字段名和付款类型值替换成报表中的名称。

Power Query 处理方法

假设原始表包含以下字段：

Protocol
Transaction Number
Activity Type
Activity Date

1. 只保留付款 activity

先筛选真正代表付款的记录：

PaymentRows =
    Table.SelectRows(
        Source,
        each [Activity Type] = "Payment"
    )

如果多个状态都可能代表付款，例如 Paid、Payment Posted，应根据业务定义列出全部有效值：

PaymentRows =
    Table.SelectRows(
        Source,
        each List.Contains(
            {"Paid", "Payment Posted"},
            [Activity Type]
        )
    )

不要仅凭日期早晚判断哪条 activity 是付款记录。

2. 将多条 activity 汇总为一笔 transaction

同一 transaction 如果有多条付款 activity，需要先确定哪一天算作该笔付款的日期。假如业务规则是取最早的付款 activity 日期，可以这样处理：

Transactions =
    Table.Group(
        PaymentRows,
        {"Protocol", "Transaction Number"},
        {
            {
                "Payment Date",
                each List.Min(List.RemoveNulls([Activity Date])),
                type nullable date
            }
        }
    )

处理后，每笔 transaction 只保留一行。

如果 Activity Date 当前是文本，应先把它转换为日期类型：

TypedRows =
    Table.TransformColumnTypes(
        Source,
        {{"Activity Date", type date}}
    )

日期文本可能存在地区格式差异。例如格式为 MM/dd/yyyy 时，应指定正确的区域设置后再转换，否则可能得到错误的日期。

3. 找出每个 protocol 的最早付款日期

如果只需要日期，可以再次分组并取最小值：

FirstPaymentByProtocol =
    Table.Group(
        Transactions,
        {"Protocol"},
        {
            {
                "First Payment Date",
                each List.Min(List.RemoveNulls([Payment Date])),
                type nullable date
            }
        }
    )

最终结果中，每个 protocol 占一行，并包含对应的首笔付款日期。

同时保留 transaction number

如果还要知道首笔付款属于哪个 transaction，不能只使用 List.Min，否则同一行中的其他字段会丢失。可以先排序，再按 protocol 保留每组的第一行：

let
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],

    TypedRows =
        Table.TransformColumnTypes(
            Source,
            {
                {"Protocol", type text},
                {"Transaction Number", type text},
                {"Activity Type", type text},
                {"Activity Date", type date}
            }
        ),

    PaymentRows =
        Table.SelectRows(
            TypedRows,
            each [Activity Type] = "Payment"
                and [Activity Date] <> null
        ),

    Transactions =
        Table.Group(
            PaymentRows,
            {"Protocol", "Transaction Number"},
            {
                {
                    "Payment Date",
                    each List.Min([Activity Date]),
                    type date
                }
            }
        ),

    SortedTransactions =
        Table.Sort(
            Transactions,
            {
                {"Protocol", Order.Ascending},
                {"Payment Date", Order.Ascending},
                {"Transaction Number", Order.Ascending}
            }
        ),

    FirstTransactionByProtocol =
        Table.Group(
            SortedTransactions,
            {"Protocol"},
            {
                {
                    "First Row",
                    each Table.FirstN(_, 1),
                    type table
                }
            }
        ),

    Result =
        Table.ExpandTableColumn(
            FirstTransactionByProtocol,
            "First Row",
            {"Transaction Number", "Payment Date"},
            {"First Transaction Number", "First Payment Date"}
        )
in
    Result

查询结果包含以下字段：

Protocol
First Transaction Number
First Payment Date

同一天有多笔付款时怎么处理

如果同一个 protocol 在最早日期有多笔 transaction，仅凭日期无法确定哪一笔才是“第一笔”。

这时需要补充明确的排序依据，例如：

精确到秒的付款时间；
transaction number；
transaction 创建序号；
系统生成的唯一 ID。

上面的示例在日期相同时，会按 Transaction Number 升序选择一条记录。这只能保证结果固定，不一定能反映真实的发生顺序。如果需要保留最早日期当天的所有 transaction，可以先计算每个 protocol 的最早日期，再将结果与 transaction 表合并，不要使用 Table.FirstN(_, 1)。

仅用 Excel 公式的简化方案

如果数据已经整理成“一笔 transaction 一行”，并且：

A 列为 Protocol
B 列为 Transaction Number
C 列为 Payment Date

可以使用：

=MINIFS($C$2:$C$500001,$A$2:$A$500001,E2)

其中，E2 是要查询的 protocol。

如果原始表仍是一笔 transaction 对应多条 activity，公式可能把非付款 activity 的日期也计算进去。这时需要增加付款类型条件：

=MINIFS(
    $D$2:$D$500001,
    $A$2:$A$500001,
    E2,
    $C$2:$C$500001,
    "Payment"
)

对于约 50 万行数据，Power Query 通常更适合清洗、分组和刷新数据，透视表则适合展示最终的汇总结果。

注意事项

先确认“付款日期”的业务定义，例如付款发起、入账、审批完成或资金实际支付日期。
排除空日期和错误日期，否则 List.Min 可能报错。
不要先按 protocol 排序，再直接删除重复项。Power Query 的去重操作不适合用来可靠地“保留最早一行”。
如果 transaction number 在不同 protocol 之间可能重复，分组键必须同时包含 Protocol 和 Transaction Number。
如果同一 transaction 包含退款、撤销或冲正记录，需要按照业务规则排除或单独处理。
如果数据已经是一笔 transaction 一行，并且只需要每个 protocol 的最早付款日期，按 protocol 分组一次即可。只有多条 activity 需要先归并为 transaction 时，才需要两级分组。