聚合重叠时间区间时如何关联对应行的附加列

核心思路 #

在合并连续/重叠时间区间的同时，通过分组内排序+累加器追踪时间块，关联每个时间块结束日期（EndMax）对应行的附加列数据。

完整M代码 #

假设你的数据源名为Source，替换成实际名称即可：

let
    Source = 你的数据源,
    // 1. 按UserID分组，保留所有列
    GroupedByID = Table.Group(Source, {"UserID"}, {{"GroupData", each _, type table [UserID=text, StartDate=date, EndDate=date, Reason=text, Area=text, Jurisdiction=text]}}),
    // 2. 处理每组数据，合并时间块并关联附加列
    ProcessGroups = Table.TransformColumns(GroupedByID, {{"GroupData", (tbl) =>
        let
            // 按StartDate升序排序，确保时间顺序正确
            Sorted = Table.Sort(tbl, {"StartDate", Order.Ascending}),
            // 添加索引，方便遍历
            AddIndex = Table.AddIndexColumn(Sorted, "Index", 0, 1, Int64.Type),
            // 用List.Accumulate生成时间块：每个块包含StartMin、EndMax、附加列
            TimeBlocks = List.Accumulate(
                List.Skip(AddIndex[Index]),
                // 初始值：第一个行作为第一个块
                {[StartMin = AddIndex[StartDate]{0}, EndMax = AddIndex[EndDate]{0}, Reason = AddIndex[Reason]{0}, Area = AddIndex[Area]{0}, Jurisdiction = AddIndex[Jurisdiction]{0}]},
                (state, currentIndex) =>
                    let
                        CurrentRow = AddIndex{currentIndex},
                        LastBlock = List.Last(state),
                        // 判断当前行是否与最后一个块重叠/连续
                        IsOverlap = CurrentRow[StartDate] <= LastBlock[EndMax]
                    in
                        if IsOverlap then
                            // 合并块：更新EndMax为较大值，同时替换附加列为当前行（因为EndMax是当前行的EndDate）
                            List.RemoveLastN(state, 1) & {[
                                StartMin = LastBlock[StartMin],
                                EndMax = List.Max({LastBlock[EndMax], CurrentRow[EndDate]}),
                                Reason = CurrentRow[Reason],
                                Area = CurrentRow[Area],
                                Jurisdiction = CurrentRow[Jurisdiction]
                            ]}
                        else
                            // 新建块：用当前行的数据
                            state & {[
                                StartMin = CurrentRow[StartDate],
                                EndMax = CurrentRow[EndDate],
                                Reason = CurrentRow[Reason],
                                Area = CurrentRow[Area],
                                Jurisdiction = CurrentRow[Jurisdiction]
                            ]}
            ),
            // 将时间块列表转成表格
            BlocksTable = Table.FromRecords(TimeBlocks)
        in
            BlocksTable
    }}),
    // 3. 展开分组后的表格，得到最终结果
    ExpandedGroups = Table.ExpandTableColumn(ProcessGroups, "GroupData", {"StartMin", "EndMax", "Reason", "Area", "Jurisdiction"}, {"StartMin", "EndMax", "Reason", "Area", "Jurisdiction"})
in
    ExpandedGroups

关键说明 #

• 排序步骤必须做：确保每组内的时间是按开始日期顺序排列，否则合并逻辑会出错。
• 累加器逻辑：每次判断当前行是否能合并到上一个时间块，若可以则更新EndMax并替换附加列为当前行（因为EndMax取自当前行的EndDate，对应附加列也取当前行）；若不行则新建一个时间块。
• 若你需要取时间块内最早行的附加列，只需把合并时的附加列赋值改成LastBlock[Reason]这类即可，按需调整。

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