Power BI大容量WorkItems表刷新失败问题及优化需求

背景 #

我有一个Power BI报表中的[WorkItems]表，包含245000条记录，原始大小90MB。该表通过30个OData Feeds（对应30个项目）直接从Azure DevOps Analytics API拉取数据，逻辑基于PowerQuery编写。

刷新报表时，[WorkItems]表持续刷新失败。移除约一半API调用（保留12个OData Feeds）后，表大小降至42MB、记录数约100000条，刷新时长2分钟（发布至Power BI Services后为3分钟，计划刷新不再失败）。

但我需要保留全部30个OData Feeds，因此有以下疑问：

1. 是否有高效快速的方法减小该表大小？
2. 是否应在数据源设置中添加参数，分块加载数据以加快刷新？
3. 我添加了当前日期前24个月的日期筛选，但未减小表大小，该如何处理？

现有PowerQuery代码（仅展示3个OData Feeds示例） #

// "$select=ParentWorkItemId, StoryPoints, State, 
WorkItemType, Title, IterationSK, AreaSK, WorkItemId" & "&$filter= 
(WorkItemType eq 'Bug' or WorkItemType eq 'User Story')", null, 
 [Implementation="2.0"]),

let
    Source =      
    OData.Feed
    ("https://analytics.dev.azure.com/MyCompany/Research and 
                    Development/_odata/v3.0-preview/WorkItems?" & 
                    "$select=ParentWorkItemId, StoryPoints, 
                    State, 
                    WorkItemType, Title, IterationSK, AreaSK, 
                    WorkItemId, 
                    Area" & "&$filter=(WorkItemType eq 'Bug' or 
                    WorkItemType eq 'User Story')" & 
                    "&$expand=Area($select=AreaPath)", null, 
                    [Implementation="2.0"]),

    #"Add all Ops & CP projects" = Table.Combine({
    Source, 
 OData.Feed("https://analytics.dev.azure.com/MyCompany/Cloud 
            Platform/_odata/v3.0-preview/WorkItems?" & 
            "$select=ParentWorkItemId, StoryPoints, State, 
            WorkItemType, 
            Title, IterationSK, AreaSK, WorkItemId, Area" & 
            "&$filter= 
            (WorkItemType eq 'Bug' or WorkItemType eq 'User 
             Story')"  & 
            "&$expand=Area($select=AreaPath)", null, 
            [Implementation="2.0"]),

 OData.Feed("https://analytics.dev.azure.com/MyCompany/Batch 
           Management/_odata/v3.0-preview/WorkItems?" & 
           "$select=ParentWorkItemId, StoryPoints, State, 
           WorkItemType, 
           Title, IterationSK, AreaSK, WorkItemId, Area" & 
           "&$filter= 
           (WorkItemType eq 'Bug' or WorkItemType eq 'User 
            Story')"  & 
           "&$expand=Area($select=AreaPath)", null, 
           [Implementation="2.0"]),}),
 #"Add AreaPath" = Table.ExpandRecordColumn(#"Add all Ops & CP 
 projects", 
 "Area", {"AreaPath"}, {"AreaPath"}),

// Calculate the date 24 months ago from the current date
   Date24MonthsAgo = Date.AddMonths(DateTime.LocalNow(), -24),

// Filter data to include only records from the last 24 months
  FilteredData = Table.SelectRows(ConvertedIDColumns, each 
  DateTime.From([CreatedDate]) >= Date24MonthsAgo),


 #"Rename Story Points to Effort" = Table.RenameColumns(#"Add 
   AreaPath", 
  {{"StoryPoints", "Effort"}}),
 #"Add Organization" = Table.AddColumn(#"Rename Story Points to 
  Effort", 
 "Organization", each "MyCompany"),
 #"Change IDs to text" = Table.TransformColumnTypes(#"Add 
   Organization", 
  {{"WorkItemId",  type text}, {"ParentWorkItemId", type text}}),
 #"Make IDs unique" = Table.TransformColumns(
 #"Change IDs to text",
 {
   {
     "WorkItemId",
     each 
       Text.Combine({(_),"-VSTS"}),
     type text
   }
 }
),

#"Make Parent IDs unique" = Table.TransformColumns(
 #"Make IDs unique",
 {
   {
     "ParentWorkItemId",
     each 
       Text.Combine({(_),"-VSTS"}),
     type text
   }
 }
),
#"Replaced Value" = Table.ReplaceValue(#"Make Parent IDs 
unique","-VSTS","",Replacer.ReplaceValue,{"ParentWorkItemId"}),
#"Parent Orphans to ""No Feature""" = 
Table.ReplaceValue(#"Replaced Value","","No 
Feature",Replacer.ReplaceValue,{"ParentWorkItemId"})


in
     #"Parent Orphans to ""No Feature"""

解答 #

1. 高效减小表大小的方法 #

• OData层前置筛选：不要先拉全量数据再在PowerQuery里过滤，把日期、工作项类型等筛选条件直接加到OData的$filter参数中，从源头减少数据拉取量。
• 裁剪冗余列：检查$select中的字段，只保留报表实际用到的列，比如IterationSK、AreaSK如果没用到就删除，避免加载无用数据。
• 优化数据类型：
  • WorkItemId和ParentWorkItemId如果不需要文本操作，保留数字类型（文本比数字占用更多存储空间）；若必须转文本，清除冗余字符。
  • 日期列仅保留date类型（无需datetime），进一步压缩数据体积。
• 去重处理：用Table.Distinct()检查并移除重复的WorkItem记录，避免重复加载同一工作项。

2. 是否需要分块加载数据 #

是的，分块加载能有效缓解刷新压力，尤其适合30个项目的场景：

• 按项目分批拉取：创建项目列表参数，用循环遍历每个项目，分批拉取并合并数据，避免一次性发起30个OData请求导致的超时或资源耗尽。
• 按日期分块：若单项目数据量仍大，可将24个月的时间范围拆分为多个小周期（如按季度），每个周期单独拉取后合并，降低单次请求的数据量。
• 控制请求频率：注意Azure DevOps Analytics API的调用限制，避免并行请求过多触发限流，可在PowerQuery中添加延迟步骤（Function.InvokeAfter）控制请求间隔。

3. 日期筛选未生效的原因及解决办法 #

从代码看，问题出在两个核心点：

• 筛选步骤逻辑错误：当前FilteredData步骤引用的ConvertedIDColumns表不存在，且筛选步骤放在列重命名、添加组织列等操作之后，属于无效筛选。
• 未在OData层做筛选：当前是拉取全量数据后再筛选，API仍返回所有历史数据，表大小自然无变化。

修正方案：

1. 将日期筛选加入OData请求：修改每个OData的$filter参数，添加日期条件，示例如下：

   "&$filter=(WorkItemType eq 'Bug' or WorkItemType eq 'User Story') and CreatedDate ge '" & DateTime.ToText(Date24MonthsAgo, "yyyy-MM-dd'T'HH:mm:ss'Z'") & "'"
   

   让API仅返回符合日期要求的数据，从源头削减数据量。
2. 调整PowerQuery筛选步骤位置：若需二次筛选，确保筛选步骤在数据合并后立即执行，避免先做无用的转换操作。

内容的提问来源于stack exchange，提问作者Hell-1931