Power Query：基于N条负数值行取消最后N条正数值行

核心步骤 #

1. 清理QTY列，提取纯数值（移除原数据中的注释文本）
2. 按PO+SID分组，统计每组内负数值的行数（即需要匹配取消的正数行数N）
3. 对每组内的正数行按原始顺序倒序标记：最后N条标记为Cancelled，其余标记为Active；所有负数行直接标记为Cancelled
4. 恢复数据的原始顺序，整理最终列结构

M 代码 #

let
    // 替换为你的实际数据源（如Excel.Workbook、SQL Server连接等）
    Source = 你的数据源,
    // 清理QTY列，提取纯数字内容
    CleanQTY = Table.TransformColumns(Source, {{"QTY", each Number.From(Text.Select(_, "-0123456789"))}}),
    // 添加原始索引，用于后续恢复数据顺序
    AddIndex = Table.AddIndexColumn(CleanQTY, "OriginalIndex", 0, 1),
    // 按PO和SID分组，计算每组负数行数
    Grouped = Table.Group(AddIndex, {"PO", "SID"}, {
        {"GroupData", each _, type table [PO=nullable text, SID=nullable text, QTY=nullable number, OriginalIndex=number]},
        {"NegativeCount", each List.Count(List.Select([QTY], (x)=>x<0))}
    }),
    // 处理每组数据，标记状态
    ProcessGroups = Table.TransformColumns(Grouped, {{"GroupData", (tbl)=>
        let
            // 拆分正负行
            PositiveRows = Table.SelectRows(tbl, each [QTY]>0),
            NegativeRows = Table.SelectRows(tbl, each [QTY]<0),
            // 获取需要标记为取消的正数行数
            CancelCount = tbl[NegativeCount]{0},
            // 对正数行按原始索引倒序，标记最后N条为Cancelled
            MarkedPositive = Table.AddColumn(PositiveRows, "Status", each 
                if List.PositionOf(List.Sort(PositiveRows[OriginalIndex], Order.Descending), [OriginalIndex]) < CancelCount 
                then "Cancelled" else "Active"),
            // 标记负数行为Cancelled
            MarkedNegative = Table.AddColumn(NegativeRows, "Status", each "Cancelled"),
            // 合并正负行数据
            Combined = Table.Combine({MarkedPositive, MarkedNegative})
        in
            Combined
    }}),
    // 展开分组数据
    Expanded = Table.ExpandTableColumn(ProcessGroups, "GroupData", {"QTY", "OriginalIndex", "Status"}),
    // 按原始索引恢复数据顺序
    Sorted = Table.Sort(Expanded, {{"OriginalIndex", Order.Ascending}}),
    // 移除临时辅助列
    Cleaned = Table.RemoveColumns(Sorted, {"OriginalIndex", "NegativeCount"}),
    // 调整列顺序（可选）
    FinalTable = Table.ReorderColumns(Cleaned, {"PO", "SID", "QTY", "Status"})
in
    FinalTable

以下SQL可直接在Power Query中通过原生SQL调用（如Value.NativeQuery或SQL Server数据源连接），逻辑与Power Query一致：

WITH CleanData AS (
    -- 清理QTY列，提取纯数值
    SELECT 
        PO,
        SID,
        CAST(REGEXP_REPLACE(QTY, '[^-0-9]', '') AS INT) AS QTY,
        -- 按PO+SID分组生成行号（若有明确顺序字段如创建时间，建议替换为该字段保证顺序稳定）
        ROW_NUMBER() OVER(PARTITION BY PO, SID ORDER BY (SELECT NULL)) AS RowNum,
        -- 统计每组内负数的行数
        SUM(CASE WHEN CAST(REGEXP_REPLACE(QTY, '[^-0-9]', '') AS INT) < 0 THEN 1 ELSE 0 END) 
            OVER(PARTITION BY PO, SID) AS NegativeCount
    FROM 你的表名 -- 替换为实际表名
),
PositiveMarked AS (
    SELECT 
        PO,
        SID,
        QTY,
        -- 正数行倒序编号，前N条标记为Cancelled
        CASE WHEN ROW_NUMBER() OVER(PARTITION BY PO, SID ORDER BY RowNum DESC) <= NegativeCount 
             THEN 'Cancelled' 
             ELSE 'Active' END AS Status,
        RowNum
    FROM CleanData
    WHERE QTY > 0
),
NegativeMarked AS (
    SELECT 
        PO,
        SID,
        QTY,
        'Cancelled' AS Status,
        RowNum
    FROM CleanData
    WHERE QTY < 0
)
SELECT PO, SID, QTY, Status 
FROM PositiveMarked
UNION ALL
SELECT PO, SID, QTY, Status 
FROM NegativeMarked
ORDER BY PO, SID, RowNum;

注意事项 #

• SQL中的REGEXP_REPLACE适用于SQL Server 2017及以上版本；若版本较低，可使用嵌套REPLACE函数清理非数字字符
• 若表中有明确的顺序字段（如记录创建时间），建议替换ORDER BY (SELECT NULL)为该字段，避免行号顺序不稳定

内容的提问来源于stack exchange，提问作者Stephane Ducci