Power Query/Power BI中计算灌装机总周转时间的逻辑求助

需要计算灌装机（Filler）的周转时间（总清洁时长），清洁作业可能当日完成或跨天进行，要求按灌装机+启动日期统计每轮独立清洁的总时长，跨天的清洁需归到启动日期下，避免拆分到两个日期。

原始数据 #

Filler  FillFromTime            FillToTime            Elapsed Time in mins
F1     6/22/23 11:00 AM         6/22/23 12:00 PM           60
F1     6/22/23 10:15 AM         6/22/23 11:00 AM           45
F1     6/22/23 5:00 AM          6/22/23 10:15 AM           315
F1     6/22/23 2:15 AM          6/22/23 5:00 AM            165
F2     6/19/23 12:30 AM         6/19/23 1:00 AM            30
F2     6/19/23 12:00 AM         6/19/23 12:30 AM           30
F2     6/18/23 5:00 PM          6/18/23 11:59 PM           419
F2     6/18/23 4:30 PM          6/18/23 5:00 PM            30
F1     6/16/23 3:00 PM          6/16/23 4:00 PM            60
F1     6/16/23 2:00 PM          6/16/23 3:00 PM            60
F1     6/16/23 1:00 PM          6/16/23 2:00 PM            60
F1     6/16/23 12:00 PM         6/16/23 1:00 PM            60
F1     6/16/23 5:00 AM          6/16/23 12:00 PM           420
F1     6/6/23 12:00 AM          6/6/23 5:00 AM             300
F1     6/5/23 9:52 PM           6/5/23 11:59 PM            127
F2     6/3/23 5:00 AM           6/3/23 9:43 AM             283
F2     6/3/23 12:00 AM          6/3/23 5:00 AM             300
F2     6/2/23 8:25 PM           6/2/23 11:59 PM            214

预期输出 #

Filler   Date     Total Turn Time
F1      6/22/23        585
F2      6/18/23        509
F1      6/16/23        660
F1      6/5/23         427
F2      6/2/23         797 

核心要求 #

• 单台灌装机的连续停机为一轮清洁，需合并连续时段的时长
• 跨天清洁的总时长归到启动日期统计，不拆分

解决方案 #

方法一：Power Query（M语言）实现 #

步骤如下：

1. 导入数据后，将FillFromTime和FillToTime列转换为日期时间类型
2. 按Filler分组，对每组数据按FillFromTime升序排序
3. 为每组添加"轮次标识"：判断当前行的FillFromTime是否等于上一行的FillToTime，如果是则属于同一轮，否则轮次+1
4. 按Filler+轮次标识分组，计算每轮的总时长，并提取该轮的最早FillFromTime的日期作为统计日期
5. 整理输出列

具体M代码（可粘贴到Power Query高级编辑器）：

let
    源 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    转换类型 = Table.TransformColumnTypes(源,{{"FillFromTime", type datetime}, {"FillToTime", type datetime}, {"Elapsed Time in mins", Int64.Type}}),
    按灌装机分组 = Table.Group(转换类型, {"Filler"}, {{"组内数据", each _, type table [Filler=text, FillFromTime=datetime, FillToTime=datetime, Elapsed Time in mins=Int64.Type]}}),
    添加轮次标识 = Table.TransformColumns(按灌装机分组, {{"组内数据", 
        (tbl) => let
            排序 = Table.Sort(tbl,{{"FillFromTime", Order.Ascending}}),
            添加索引 = Table.AddIndexColumn(排序, "索引", 0, 1, Int64.Type),
            添加轮次 = Table.AddColumn(添加索引, "轮次", 
                (row) => if row[索引] = 0 then 1 
                        else if row[FillFromTime] = 添加索引{row[索引]-1}[FillToTime] then 添加索引{row[索引]-1}[轮次] 
                        else 添加索引{row[索引]-1}[轮次] + 1
            )
        in 添加轮次
    }}),
    展开组内数据 = Table.ExpandTableColumn(添加轮次标识, "组内数据", {"FillFromTime", "Elapsed Time in mins", "轮次"}, {"FillFromTime", "Elapsed Time in mins", "轮次"}),
    按灌装机和轮次分组 = Table.Group(展开组内数据, {"Filler", "轮次"}, {{"总时长", each List.Sum([Elapsed Time in mins]), Int64.Type}, {"启动日期", each Date.From(List.Min([FillFromTime])), type date}}),
    整理列 = Table.SelectColumns(按灌装机和轮次分组, {"Filler", "启动日期", "总时长"}),
    重命名列 = Table.RenameColumns(整理列,{{"启动日期", "Date"}, {"总时长", "Total Turn Time"}})
in
    重命名列

方法二：Power BI中用DAX实现 #

如果不想修改原始数据，可通过DAX创建计算表和度量值：

步骤1：创建计算表生成轮次标识

清洁轮次表 = 
VAR 排序后数据 = 
    ADDCOLUMNS(
        ALL('原始数据'),
        "排序索引", RANKX(FILTER('原始数据', '原始数据'[Filler] = EARLIER('原始数据'[Filler])), '原始数据'[FillFromTime],, ASC)
    )
VAR 添加轮次 = 
    ADDCOLUMNS(
        排序后数据,
        "轮次",
        VAR 当前索引 = [排序索引]
        VAR 当前灌装机 = [Filler]
        VAR 上一行时间 = 
            MAXX(
                FILTER(排序后数据, [Filler] = 当前灌装机 && [排序索引] = 当前索引 - 1),
                [FillToTime]
            )
        RETURN
            IF(当前索引 = 1, 1, IF([FillFromTime] = 上一行时间, MAXX(FILTER(排序后数据, [Filler] = 当前灌装机 && [排序索引] = 当前索引 - 1), [轮次]), MAXX(FILTER(排序后数据, [Filler] = 当前灌装机 && [排序索引] = 当前索引 - 1), [轮次]) + 1))
    )
RETURN
    添加轮次

步骤2：创建度量值计算总周转时间

总周转时间 = 
CALCULATE(
    SUM('原始数据'[Elapsed Time in mins]),
    ALLEXCEPT('清洁轮次表', '清洁轮次表'[Filler], '清洁轮次表'[轮次])
)

步骤3：添加启动日期计算列

在清洁轮次表中添加计算列：

启动日期 = DATEVALUE(MINX(FILTER('清洁轮次表', [Filler] = EARLIER([Filler]) && [轮次] = EARLIER([轮次])), [FillFromTime]))

最后将Filler、启动日期和总周转时间放入视觉对象即可得到预期结果。

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