Power BI中如何通过M查询基于ID分组首末记录创建新变量

核心逻辑：你之前的写法无法生效是因为M查询没有直接获取当前行所属分组首尾行的内置属性，需要先标记每个分组内的行序号，再判断是否为首/尾行后赋值。

实现步骤 #

• 步骤1：为原始表添加全局索引列，保证原始行顺序不被分组操作打乱
• 步骤2：按pkBookingItemID字段分组，分组操作选择保留所有行
• 步骤3：为每个分组内的行添加组内序号、组内行数两个标记字段
• 步骤4：展开分组表，根据组内序号判断是否为首尾行，计算两个新字段

完整M代码示例 #

let
    // 替换这里的"你的原始表"为你自己的数据源引用
    源 = 你的原始表,
    // 添加全局索引保证原始顺序不丢失
    添加全局索引 = Table.AddIndexColumn(源, "全局索引", 0, 1, Int64.Type),
    // 按pkBookingItemID分组
    分组 = Table.Group(添加全局索引, {"pkBookingItemID"}, {{"组内所有行", each _, type table [pkBookingItemID=number, StartTime=time, EndTime=time, BusinessStartTime=time, BusinessEndTime=time, 全局索引=number]}}),
    // 为每个分组添加组内行号、组内总行数标记
    添加组内标记 = Table.TransformColumns(分组, {"组内所有行", (t) => 
        let
            加行号 = Table.AddIndexColumn(t, "组内行号", 1, 1, Int64.Type),
            总行数 = Table.RowCount(加行号),
            加总行数标记 = Table.AddColumn(加行号, "组内总行数", each 总行数, Int64.Type)
        in
            加总行数标记
    }),
    // 展开分组的所有行
    展开分组 = Table.ExpandTableColumn(添加组内标记, "组内所有行", {"StartTime", "EndTime", "BusinessStartTime", "BusinessEndTime", "全局索引", "组内行号", "组内总行数"}),
    // 按全局索引排序恢复原始表顺序
    恢复原始顺序 = Table.Sort(展开分组,{{"全局索引", Order.Ascending}}),
    // 计算New_BusinessStartTime
    添加新开始时间 = Table.AddColumn(恢复原始顺序, "New_BusinessStartTime", each if [组内行号] = 1 then [StartTime] else [BusinessStartTime], type time),
    // 计算New_BusinessEndTime
    添加新结束时间 = Table.AddColumn(添加新开始时间, "New_BusinessEndTime", each if [组内行号] = [组内总行数] then [EndTime] else [BusinessEndTime], type time),
    // 移除不需要的辅助列
    清理辅助列 = Table.RemoveColumns(添加新结束时间,{"全局索引", "组内行号", "组内总行数"})
in
    清理辅助列

注意事项 #

• 如果你的时间字段是文本类型而非time类型，代码里的类型声明可以对应修改为text，不影响逻辑运行
• 如果原始表已经有可保证分组内顺序的唯一排序字段，可以省略全局索引的步骤，直接用已有字段排序即可

内容的提问来源于stack exchange，提问作者Deepak Kumar