PowerBI自定义列：按Order Number分组，以上一条End Date作为Start Date

需求说明 #

需要为交易记录表添加Start Date自定义列，规则如下：

• 同一Order Number（订单号）分组内，当前记录的Start Date等于上一条记录的End Date
• 当Order Number变化时，新订单的第一条记录Start Date留空

当前数据表 #

Order Number | Item Number | Operations Sequence Number | End Date
-------------------------------------------------------------------
1486120      | GM7666-1    | 265                        | 03/02/2023
1486120      | GM7666-1    | 300                        | 03/02/2023
1486120      | GM7666-1    | 301                        | 03/06/2023
1486120      | GM7666-1    | 310                        | 03/06/2023
1486120      | GM7666-1    | 320                        | 03/10/2023

预期结果 #

Order Number | Item Number | Operations Sequence Number | End Date   | Start Date
-------------------------------------------------------------------------------
1486120      | GM7666-1    | 265                        | 03/02/2023 | 
1486120      | GM7666-1    | 300                        | 03/02/2023 | 03/02/2023
1486120      | GM7666-1    | 301                        | 03/06/2023 | 03/06/2023
1486120      | GM7666-1    | 310                        | 03/06/2023 | 03/06/2023
1486120      | GM7666-1    | 320                        | 03/10/2023 | 03/10/2023
1486120      | GM7666-1    | 330                        | 03/21/2023 | 03/10/2023
1486120      | GM7666-1    | 335                        | 03/23/2023 | 03/21/2023
1486120      | GM7666-1    | 340                        | 04/04/2023 | 03/23/2023

解决方案 #

方案1：Power Query（Excel/Power BI） #

通过分组+添加偏移列实现，以下是直接可复用的M语言代码：

let
    源 = 你的数据源, // 替换为实际数据源
    // 按订单号+物料号分组，每组内按工序序号升序排序
    分组排序 = Table.Group(源, {"Order Number", "Item Number"}, {{"分组数据", each Table.Sort(_, {{"Operations Sequence Number", Order.Ascending}})}}),
    // 为每组添加Start Date列
    添加StartDate列 = Table.TransformColumns(分组排序, {"分组数据", (tbl) => 
        let
            添加索引 = Table.AddIndexColumn(tbl, "临时索引", 0, 1),
            取上一行日期 = Table.AddColumn(添加索引, "Start Date", each if [临时索引] = 0 then null else 添加索引{[临时索引]-1}[End Date]),
            移除临时索引 = Table.RemoveColumns(取上一行日期, {"临时索引"})
        in
            移除临时索引
    }),
    // 展开分组数据，得到最终结果
    展开结果 = Table.ExpandTableColumn(添加StartDate列, "分组数据", {"Operations Sequence Number", "End Date", "Start Date"})
in
    展开结果

方案2：SQL（数据库场景） #

使用窗口函数LAG()，按订单号分区、工序序号排序，直接获取上一行的End Date：

SELECT 
    [Order Number],
    [Item Number],
    [Operations Sequence Number],
    [End Date],
    -- 同一订单+物料组内，取上一行的End Date，第一条记录返回NULL
    LAG([End Date]) OVER (PARTITION BY [Order Number], [Item Number] ORDER BY [Operations Sequence Number]) AS [Start Date]
FROM 
    你的表名 -- 替换为实际表名
ORDER BY 
    [Order Number], [Item Number], [Operations Sequence Number];

内容的提问来源于stack exchange，提问作者Guillermo Luna