Power BI中用Power Query间隔与孤岛法提取员工缺勤起止日期

需求说明 #

需要按Project ID、Person ID、Time Status分组，识别同一维度下的连续缺勤日期段，提取每个段的起止日期——按月年分组无法处理同一月份内的多个非连续缺勤段，因此需要用连续日期识别的方法。

示例输入数据 #

期望输出 #

方法1：Power Query编辑器处理（推荐，性能更优） #

核心逻辑是为每个连续的缺勤日期段分配唯一标识，再分组提取起止日期。

步骤1：导入数据并排序 #

将数据源导入Power Query编辑器，按Project ID、Person ID、Time Status、Date升序排序，确保日期顺序正确。

步骤2：添加索引列 #

点击添加列 → 索引列 → 从0开始，生成用于计算日期差的索引。

步骤3：计算相邻日期差 #

添加自定义列，计算当前行与上一行的日期天数差：

DateDiff = if [Index] = 0 then 1 else Duration.Days([Date] - #"Added Index"{[Index]-1}[Date])

步骤4：标记新缺勤段 #

添加自定义列，判断是否为新的缺勤段（维度变更或日期不连续）：

IsNewSegment = 
if [Index] = 0 then true 
else [DateDiff] > 1 
    or [Project ID] <> #"Added Index"{[Index]-1}[Project ID] 
    or [Person ID] <> #"Added Index"{[Index]-1}[Person ID] 
    or [Time Status] <> #"Added Index"{[Index]-1}[Time Status]

步骤5：生成段ID #

添加自定义列，为每个连续段生成唯一ID：

SegmentID = List.Accumulate(List.Range(#"Added Custom2"[IsNewSegment],0,[Index]),0,(state,current)=>state+if current then 1 else 0)

步骤6：分组提取起止日期 #

点击转换 → 分组依据，按Project ID、Person ID、Time Status、SegmentID分组，设置：

• 新列Start Date：操作选最小值，列选Date
• 新列End Date：操作选最大值，列选Date

步骤7：清理列 #

删除SegmentID、Index、DateDiff、IsNewSegment等冗余列，保留需要的字段即可。

方法2：DAX模型端处理 #

如果更倾向于在数据模型中实现，可通过计算列和汇总表完成：

步骤1：添加计算列标记新段 #

IsNewSegment = 
VAR PrevDate = 
    CALCULATE(MAX('Table'[Date]), 
        FILTER('Table', 
            'Table'[Project ID] = EARLIER('Table'[Project ID]) && 
            'Table'[Person ID] = EARLIER('Table'[Person ID]) && 
            'Table'[Time Status] = EARLIER('Table'[Time Status]) && 
            'Table'[Date] < EARLIER('Table'[Date])
        ))
RETURN
    ISBLANK(PrevDate) || DATEDIFF(PrevDate, 'Table'[Date], DAY) > 1

步骤2：添加计算列生成段ID #

SegmentID = 
CALCULATE(COUNTROWS(FILTER('Table', 
    'Table'[Project ID] = EARLIER('Table'[Project ID]) && 
    'Table'[Person ID] = EARLIER('Table'[Person ID]) && 
    'Table'[Time Status] = EARLIER('Table'[Time Status]) && 
    'Table'[Date] <= EARLIER('Table'[Date]) && 
    'Table'[IsNewSegment] = TRUE()
)))

步骤3：创建汇总表 #

AbsenceSummary = 
SUMMARIZE('Table', 
    'Table'[Project ID], 
    'Table'[Person ID], 
    'Table'[Time Status], 
    'Table'[SegmentID], 
    "Start Date", MIN('Table'[Date]), 
    "End Date", MAX('Table'[Date])
)

最后删除AbsenceSummary中的SegmentID列，即可得到期望的结果。

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