如何在Power BI Desktop中查询Azure DevOps看板列的进入和退出时间

调整OData查询获取看板字段 #

原有查询无法获取看板相关信息，是因为Azure DevOps OData接口默认不会返回全部实体字段，需要在请求中显式指定需要的看板字段，调整后的查询代码如下：

let
    Source = OData.Feed("https://analytics.dev.azure.com/wintellectaccelins247playpens/WorkItemPlaypen04/_odata/v3.0-preview/WorkItemRevisions?$filter=Area/AreaPath eq 'WorkItemPlayPen04\Accelins247\Intelligent Ingestion\Submission Monitoring' and WorkItemType eq 'User Story'&$select=WorkItemId,Revision,RevisedDate,Title,BoardName,BoardColumnName,BoardColumnDone&$expand=Area", null, [Implementation="2.0"])
in
    Source

如果工作项归属于多个看板，后续处理前可以先按BoardName字段筛选对应看板的数据集，避免不同看板的数据混淆

Power Query脚本计算列进出时间 #

拿到包含看板字段的修订数据后，按以下逻辑处理即可得到每个工作项在各看板列的进入、退出时间：

• 按WorkItemId升序、Revision升序对全量数据排序，保证修订版本顺序正确
• 以WorkItemId和BoardName为维度分组，每个分组保留所有修订行
• 对每个分组添加偏移列，提取上一条修订的BoardColumnName和RevisedDate
• 筛选出BoardColumnName发生变更的行，当前行的RevisedDate即为进入当前列的时间，下一条修订的RevisedDate即为当前列的退出时间
• 每个工作项当前所在列的退出时间可填充为统计截止时间或当前日期

完整的处理脚本参考：

let
    // 引用上面调整后的OData查询结果，这里假设查询名称为「工作项修订数据」
    Source = #"工作项修订数据",
    // 排序保证修订顺序
    排序 = Table.Sort(Source,{{"WorkItemId", Order.Ascending}, {"Revision", Order.Ascending}}),
    // 按工作项ID和看板名称分组
    分组 = Table.Group(排序, {"WorkItemId", "BoardName"}, {{"所有修订", each _, type table [WorkItemId=nullable number, Revision=nullable number, RevisedDate=nullable datetimezone, BoardColumnName=nullable text, BoardColumnDone=nullable logical]}}),
    // 处理每个分组的修订记录
    计算进出时间 = Table.AddColumn(分组, "列停留记录", (x) => 
        let
            修订表 = x[所有修订],
            // 添加上一列的名称和时间
            添加上一行信息 = Table.AddColumn(Table.AddIndexColumn(修订表, "索引", 0, 1, Int64.Type), "上一列名称", each try 修订表[BoardColumnName]{[索引]-1} otherwise null),
            添加上一行时间 = Table.AddColumn(添加上一行信息, "上一行修订时间", each try 修订表[RevisedDate]{[索引]-1} otherwise null),
            // 筛选列变更的行
            筛选列变更 = Table.SelectRows(添加上一行时间, each [BoardColumnName] <> [上一列名称] or [索引] = 0),
            // 添加退出时间
            添加退出时间 = Table.AddColumn(Table.AddIndexColumn(筛选列变更, "变更索引", 0, 1, Int64.Type), "退出时间", each try 筛选列变更[RevisedDate]{[变更索引]+1} otherwise DateTime.LocalNow()),
            // 重命名进入时间列
            重命名列 = Table.RenameColumns(添加退出时间, {{"RevisedDate", "进入时间"}}),
            // 保留需要的字段
            结果 = Table.SelectColumns(重命名列, {"WorkItemId", "BoardName", "BoardColumnName", "BoardColumnDone", "进入时间", "退出时间"})
        in
            结果
    ),
    // 展开所有分组的结果
    展开结果 = Table.ExpandTableColumn(计算进出时间, "列停留记录", {"BoardColumnName", "BoardColumnDone", "进入时间", "退出时间"}, {"BoardColumnName", "BoardColumnDone", "进入时间", "退出时间"})
in
    展开结果

注意事项 #

• 如果看板列区分「完成/未完成」两个子状态，可以根据BoardColumnDone字段判断是否需要合并同一列两个子状态的停留时长
• 计算停留时长可以直接用退出时间 - 进入时间得到duration类型的结果，按需转换为小时、天即可

内容的提问来源于stack exchange，提问作者David V. Corbin