Power BI中合并活动列并生成对应end_date的实现求助

原始数据表 #

需求说明 #

• 将多个activity列合并为单个activity列，保留record_id、site和start_date的行值；
• 创建end_date列：取值为对应活动首次出现空值后的下一个start_date，若后续无空值则取最后一个start_date。

期望结果 #

现有尝试代码及问题 #

以下代码仅获取活动所在行的下一个start_date，未判断后续行中该活动是否为空，无法匹配预期结果：

let
    // 步骤1：加载数据
    Source = Sheet2,
    // 步骤2：将活动列逆透视为单列
    UnpivotedColumns = Table.UnpivotOtherColumns(Source, {"record_id", "Site", "start_date"}, "ActivityType", "activity"),
    // 步骤3：移除活动为空的行
    FilteredRows = Table.SelectRows(UnpivotedColumns, each ([activity] <> null and [activity] <> "")),
    // 步骤4：按record_id、Site、start_date排序
    SortedTable = Table.Sort(FilteredRows, {{"record_id", Order.Ascending}, {"Site", Order.Ascending}, {"start_date", Order.Ascending}}),
    // 步骤5：创建唯一的start_date列表
    DistinctStartDates = Table.Distinct(SortedTable[[record_id], [Site], [start_date]]),
    // 步骤6：为每个分组添加下一个start_date
    AddedNextStartDate = Table.AddColumn(DistinctStartDates, "next_start_date", each 
        let 
            CurrentRecordID = [record_id],
            CurrentSite = [Site],
            CurrentStartDate = [start_date],
            // 查找后续的start_date
            NextDates = Table.SelectRows(DistinctStartDates, each ([record_id] = CurrentRecordID and [Site] = CurrentSite and [start_date] > CurrentStartDate)),
            NextStartDate = if Table.RowCount(NextDates) > 0 then NextDates[start_date]{0} else null
        in 
            NextStartDate, type nullable date
    ),
    // 步骤7：合并回主表
    MergedTable = Table.NestedJoin(SortedTable, {"record_id", "Site", "start_date"}, AddedNextStartDate, {"record_id", "Site", "start_date"}, "NextDateTable", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "NextDateTable", {"next_start_date"}),
    // 步骤8：为空的end_date填充最大日期
    MaxEndDate = List.Max(Source[start_date]),
    FinalTable = Table.TransformColumns(ExpandedTable, {"next_start_date", each if _ = null then MaxEndDate else _, type date}),
    // 步骤9：重命名列并排序
    RenamedTable = Table.RenameColumns(FinalTable, {{"next_start_date", "end_date"}}),
    #"Sorted Rows" = Table.Sort(RenamedTable,{{"ActivityType", Order.Ascending}})
in
    #"Sorted Rows"

解决方案代码 #

以下代码实现了需求逻辑：先保留所有日期的活动状态，再为每个活动的有效行找到首次缺失后的下一个日期：

let
    // 步骤1：加载原始数据
    Source = Sheet2,
    // 步骤2：按日期排序，确保日期顺序正确
    SortedSource = Table.Sort(Source, {{"record_id", Order.Ascending}, {"site", Order.Ascending}, {"start_date", Order.Ascending}}),
    // 步骤3：逆透视所有活动列，保留空值（用于后续判断活动是否缺失）
    UnpivotedAll = Table.UnpivotOtherColumns(SortedSource, {"record_id", "site", "start_date"}, "ActivityColumn", "activity"),
    // 步骤4：获取所有唯一的日期列表（用于查找后续日期）
    AllDates = Table.Distinct(SortedSource[[record_id], [site], [start_date]]),
    // 步骤5：为每个活动行，找到对应的end_date
    AddedEndDate = Table.AddColumn(UnpivotedAll, "end_date", each 
        let
            CurrentRecord = [record_id],
            CurrentSite = [site],
            CurrentDate = [start_date],
            CurrentActivity = [activity],
            // 筛选当前record_id和site下，日期晚于当前日期的所有行
            SubsequentDates = Table.SelectRows(AllDates, each [record_id] = CurrentRecord and [site] = CurrentSite and [start_date] > CurrentDate),
            // 在后续日期中，找到第一个当前活动为空的日期
            FirstMissingDate = List.First(
                Table.SelectRows(UnpivotedAll, 
                    each [record_id] = CurrentRecord and [site] = CurrentSite and [start_date] > CurrentDate and [activity] = CurrentActivity and ([activity] = null or [activity] = "")
                )[start_date]
            ),
            // 如果找到缺失日期，end_date就是该日期；否则取最后一个日期
            EndDate = if FirstMissingDate <> null then FirstMissingDate else List.Max(AllDates[start_date])
        in
            EndDate, type date
    ),
    // 步骤6：筛选出活动非空的行（即我们需要的最终行）
    FilteredValidRows = Table.SelectRows(AddedEndDate, each [activity] <> null and [activity] <> ""),
    // 步骤7：移除多余的ActivityColumn列，整理结果
    CleanedTable = Table.RemoveColumns(FilteredValidRows, {"ActivityColumn"}),
    // 步骤8：按活动和日期排序，匹配期望结果的顺序
    FinalSortedTable = Table.Sort(CleanedTable, {{"activity", Order.Ascending}, {"start_date", Order.Ascending}})
in
    FinalSortedTable

代码说明 #

1. 保留空值逆透视：不同于原代码直接过滤空值，这里保留所有活动的空值记录，用于后续判断活动在哪些日期缺失。
2. 查找首次缺失日期：为每个活动的有效行，在后续日期中找到第一个该活动为空的日期，作为end_date。
3. 填充最终日期：如果活动在后续所有日期都存在，则取最大的start_date作为end_date。

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