PowerQuery新增AVG_TIME_DIFF列：计算同A值行平均时间差

原始数据表格 #

注：COUNT列仅对ID非空的行生效，统计同A值且B等于“b”的行数。

AVG_TIME_DIFF列规则 #

• 仅对ID非空的行进行处理
• 若COUNT值为0，返回“0”
• 若COUNT值不为0，取同A值且B等于“b”的所有行的Timestamp，加上当前行的Timestamp，按时间排序后计算平均时间差（单位：秒，小数部分可按需取舍）
• 其余行结果为空

预期结果 #

PowerQuery M代码实现 #

加载原始数据进入PowerQuery编辑器后，添加自定义列AVG_TIME_DIFF，输入以下代码：

if [ID] <> null then
    if [COUNT] = 0 then
        "0"
    else
        let
            currentA = [A],
            currentTS = [Timestamp],
            filteredTS = Table.SelectRows(Source, each [A] = currentA and [B] = "b")[Timestamp],
            allTS = List.Sort(List.Combine({filteredTS, {currentTS}})),
            timeDiffs = List.Generate(
                () => [index=1, diff=Duration.TotalSeconds(allTS{index} - allTS{index-1})],
                each [index] < List.Count(allTS),
                each [index=[index]+1, diff=Duration.TotalSeconds(allTS{index} - allTS{index-1})],
                each [diff]
            ),
            avgDiff = Number.Round(List.Average(timeDiffs), 0)
        in
            Text.From(avgDiff)
else
    null

代码说明 #

• 先判断ID是否非空，仅对非空行执行计算逻辑
• COUNT为0时直接返回"0"
• 筛选同A且B="b"的所有Timestamp，合并当前行Timestamp后排序
• 计算相邻时间的秒级差值，取平均值并取整后转为文本格式
• ID为空的行返回空值

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