Azure DevOps Analytics OData查询优化：筛选WorkItem后获取Revisions

问题背景 #

需要实现以下查询逻辑：仅当WorkItem处于打开状态或其ClosedDate≥2023年1月1日时，获取该WorkItem的Revisions数据；且必须先完成WorkItem的筛选操作，再获取对应Revisions。

当前使用Power Query编写的OData查询在ExpandRevisions步骤触发400错误，错误信息如下：

DataSource.Error: OData: Falha na solicitação: O servidor remoto retornou um erro: (400) Solicitação Incorreta. (VS403483: The query specified in the URI is not valid: VS403489: The Analytics Service doesn't support key or property navigation like WorkItems(Id) or WorkItem(Id)/AssignedTo. If you getting that error in PowerBI, please, rewrite your query to avoid incorrect folding that causes N+1 problem..)

尝试通过函数匹配WorkItemId筛选Revisions表时，因数据量较大导致查询耗时极长。

错误原因 #

Azure DevOps Analytics服务不支持通过单个WorkItem的导航路径（如WorkItems(73592)/Revisions）拉取关联数据，Power Query默认的ExpandTableColumn操作会触发N+1请求（每个WorkItem单独发送一次Revisions请求），既触发服务端报错，又严重影响查询效率。

优化方案 #

核心思路是直接从Revisions表发起查询，通过关联WorkItem的筛选条件在服务端完成过滤，利用OData的查询折叠特性，让服务器只返回符合条件的数据，避免客户端处理全量数据或N+1请求：

• 先筛选出符合条件的WorkItem，仅保留WorkItemId字段以减少数据传输量
• 从Revisions表查询，通过WorkItem/WorkItemId关联筛选后的WorkItemId列表，直接获取符合要求的Revisions数据
• 基于筛选后的Revisions数据完成后续的展开、分组等操作

优化后的完整代码 #

let
    // OData数据源基础地址
    Fonte = OData.Feed("https://analytics.dev.azure.com/company/teamProject/_odata/v4.0-preview/", null, [Implementation="2.0"]),
    
    // 第一步：筛选符合条件的WorkItem，仅保留WorkItemId
    WorkItems_table = Fonte{[Name="WorkItems",Signature="table"]}[Data],
    FilterClosedDate = Table.SelectRows(WorkItems_table,
        each ([State] = "Open" or [State] = "In Progress" or [State] = "Under Review" // 替换为你的"打开状态"列表
             or ([ClosedDate] <> null and [ClosedDate] >= DateTime.AddZone(#datetime(2023, 1, 1,0,0,0), -3, 00))
        )
    ),
    FilterWiType = Table.SelectRows(FilterClosedDate,
        each List.Contains({"Improvement", "Data Query", "Data Fix", "Defect", "Support"}, [WorkItemType])
    ),
    FilterState = Table.SelectRows(FilterWiType,
        each not List.Contains({"Removed", "Rejected", "Expurgated"}, [State])
    ),
    // 只保留WorkItemId，减少数据传输
    FilteredWorkItemIds = Table.SelectColumns(FilterState, {"WorkItemId"}),
    
    // 第二步：从Revisions表直接筛选关联符合条件的WorkItem
    Revisions_table = Fonte{[Name="Revisions",Signature="table"]}[Data],
    // 关联筛选后的WorkItemId，利用查询折叠让服务端完成过滤
    FilteredRevisions = Table.SelectRows(Revisions_table,
        each List.Contains(FilteredWorkItemIds[WorkItemId], [WorkItem/WorkItemId])
    ),
    
    // 后续操作基于筛选后的Revisions数据
    #"Expand BoardLocations" = Table.ExpandTableColumn(FilteredRevisions, "BoardLocations", {"ColumnName", "IsDone"}),
    // 按WorkItemId、ColumnName、IsDone分组，计算最小ChangedDate
    #"Group by WorkItemId and BoardLocations" = Table.Group(
        #"Expand BoardLocations",
        {"WorkItem/WorkItemId", "ColumnName", "IsDone"},
        {{"MinChangedDate", each List.Min([ChangedDate]), type datetime}}
    ),
    // 重命名WorkItemId列并选择需要的列
    #"Rename WorkItemId Column" = Table.RenameColumns(#"Group by WorkItemId and BoardLocations", {{"WorkItem/WorkItemId", "WorkItemId"}}),
    #"Select Columns" = Table.SelectColumns(#"Rename WorkItemId Column", {"WorkItemId", "ColumnName", "IsDone", "MinChangedDate"}),
    #"Linhas Classificadas" = Table.Sort(#"Select Columns",{{"WorkItemId", Order.Ascending}})
in
    #"Linhas Classificadas"

代码说明 #

• 修正了原WorkItem状态筛选的逻辑错误：将State <> "Removed" or State <> "Rejected"改为not List.Contains(...)，确保正确排除指定状态
• 用List.Contains简化WorkItem类型筛选，代码更简洁易维护
• 先筛选WorkItem并仅保留WorkItemId，再关联Revisions表，利用查询折叠让服务端完成数据过滤，彻底避免N+1请求
• 直接从Revisions表获取数据，规避了服务端不支持的导航路径请求

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