Power BI统计连续有值行及获取起始日期与最高等级问题求助

问题分析 #

你的代码核心问题在于提前过滤了空值行，导致无法识别原始数据中AE列空值造成的连续中断。比如record_id=10的hb_ae在12/9/2015的行是空，这行是连续组的中断点，但你过滤空值后，前后的有值行被当成了连续的，从而统计错误。

正确思路是保留所有原始行，先标记每个AEType是否有值，再基于原始排序判断连续组，最后过滤无值行进行统计。

修正后的代码 #

Source = table_name,

// 统一数据类型，确保AE列数值类型一致
ChangedTypes = Table.TransformColumnTypes(
    Source,
    {
        {"record_id", type text},
        {"visit_date", type date},
        {"int_date", type date},
        {"sched", type text},
        {"hb_ae", Int64.Type},
        {"wbc_ae", Int64.Type},
        {"anc_ae", Int64.Type},
        {"plt_ae", Int64.Type},
        {"ast_ae", Int64.Type},
        {"alt_ae", Int64.Type},
        {"arc_hb_ae", Int64.Type}
    }
),

// 逆透视AE列，保留所有行（包括空值）
UnpivotedAll = Table.Unpivot(
    ChangedTypes,
    {"hb_ae","wbc_ae","anc_ae","plt_ae","ast_ae","alt_ae","arc_hb_ae"},
    "AEType",
    "Grade"
),

// 按record_id、AEType、就诊日期排序，确保顺序正确
Sorted = Table.Sort(
    UnpivotedAll,
    {
        {"record_id", Order.Ascending},
        {"AEType", Order.Ascending},
        {"visit_date", Order.Ascending}
    }
),

// 分组标记连续组：判断当前行是否与上一行属于同一连续组
GroupedWithContinuation = Table.Group(
    Sorted,
    {"record_id", "AEType"},
    {
        {"GroupedRows", each 
            let
                // 添加行索引，用于获取上一行数据
                Indexed = Table.AddIndexColumn(_, "RowIndex", 0, 1),
                // 标记连续组起始行：当前行有值，且上一行无值/是第一行
                MarkStart = Table.AddColumn(Indexed, "IsGroupStart", each 
                    [Grade] <> null and (
                        [RowIndex] = 0 or 
                        Indexed[Grade]{[RowIndex]-1} = null
                    )
                ),
                // 生成连续组唯一标识：累计起始行数量
                AddGroupKey = Table.AddColumn(MarkStart, "GroupKey", each 
                    List.Count(List.Range(MarkStart[IsGroupStart], 0, [RowIndex]+1, 1))
                )
            in
                AddGroupKey
        }
    }
),

// 展开分组后的行数据
ExpandedGrouped = Table.ExpandTableColumn(
    GroupedWithContinuation,
    "GroupedRows",
    {"visit_date", "int_date", "sched", "Grade", "GroupKey"}
),

// 过滤掉无AE记录的空值行
FilteredValid = Table.SelectRows(ExpandedGrouped, each [Grade] <> null),

// 按连续组分组统计所需字段
FinalGrouped = Table.Group(
    FilteredValid,
    {"record_id", "AEType", "int_date", "sched", "GroupKey"},
    {
        {"AEDate", each List.Min([visit_date]), type date},
        {"AEs", each Table.RowCount(_), Int64.Type},
        {"MaxGrade", each List.Max([Grade]), Int64.Type}
    }
),

// 调整列顺序并排序
FinalResult = Table.ReorderColumns(
    FinalGrouped,
    {"record_id", "AEType", "int_date", "AEDate", "AEs", "MaxGrade", "sched"}
),
FinalSorted = Table.Sort(FinalResult, {{"record_id", Order.Ascending}, {"AEDate", Order.Ascending}})

in
FinalSorted

修正要点说明 #

1. 保留全量数据：不再提前过滤空值，确保能捕捉到AE列空值导致的连续中断。
2. 连续组标识生成：通过对比当前行与上一行的AE值存在状态，标记连续组起始点，再累计起始点数量生成唯一GroupKey，确保每个连续组独立区分。
3. 类型统一处理：明确指定AE列的数值类型，避免排序、统计时出现类型错误。

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