Power Query增量更新Power BI表：限定日期范围或上次更新时间

现有Power Query代码从ADO按固定日期拉取数据，导致数据下载量过大。需求：

• 最优方案：仅拉取上次查询运行后的新记录
• 备选方案：限定拉取最近若干天的数据
• 最终将返回的数据追加至现有表中

当前查询代码：

let
    Source = OData.Feed("https:......?$apply=filter((Team/TeamName eq 'DXC Scrum Team 1' or Team/TeamName eq 'DXC Scrum Team 2' ) and BoardName eq 'Evolve Requirements' and DateValue ge 2024-07-20Z)/groupby((DateValue, Team/TeamName ,Custom_BlockedStatus,State,ColumnName,LaneName,WorkItemID,ParentWorkItemID,WorkItemType,Title,CreatedDate,ActivatedDate,ResolvedDate,ClosedDate,ChangedDate,Custom_DefectSeverity,Custom_DIFRelease,Changedby/Username,CreatedBy/Username, AssignedTo/UserName,Area/AreaPath,Iteration/IterationPath),aggregate($count as Count))", null, [Implementation="2.0"]),
    #"Expanded Team" = Table.ExpandRecordColumn(Source, "Team", {"TeamName"}, {"Team.TeamName"}),
    #"Expanded Area" = Table.ExpandRecordColumn(#"Expanded Team", "Area", {"AreaPath"}, {"Area.AreaPath"}),
    #"Expanded AssignedTo" = Table.ExpandRecordColumn(#"Expanded Area", "AssignedTo", {"UserName"}, {"AssignedTo.UserName"}),
    #"Expanded ChangedBy" = Table.ExpandRecordColumn(#"Expanded AssignedTo", "ChangedBy", {"UserName"}, {"ChangedBy.UserName"}),
    #"Expanded CreatedBy" = Table.ExpandRecordColumn(#"Expanded ChangedBy", "CreatedBy", {"UserName"}, {"CreatedBy.UserName"}),
    #"Expanded Iteration" = Table.ExpandRecordColumn(#"Expanded CreatedBy", "Iteration", {"IterationPath"}, {"Iteration.IterationPath"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Iteration", "De-Duplication", each if [State] = "Completed" and
[DateValue] > Date.AddDays([ClosedDate],15) then "Exclude" else "Inculde"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"De-Duplication"] = "Inculde"))
in
    #"Filtered Rows"

备选方案：限定拉取最近N天数据 #

核心思路：动态生成日期条件，替换OData查询中的固定日期，只拉取指定天数内的记录

修改后的代码：

let
    // 定义拉取最近N天的数据，可修改为7表示一周
    DaysToPull = 2,
    // 计算UTC起始日期，转成OData要求的ISO格式
    StartDate = DateTime.UtcNow() - #duration(DaysToPull, 0, 0, 0),
    StartDateText = Text.Format("yyyy-MM-ddTHH:mm:ssZ", StartDate),
    // 动态拼接OData查询URL，替换固定日期为动态生成的日期
    Source = OData.Feed("https:......?$apply=filter((Team/TeamName eq 'DXC Scrum Team 1' or Team/TeamName eq 'DXC Scrum Team 2' ) and BoardName eq 'Evolve Requirements' and DateValue ge " & StartDateText & ")/groupby((DateValue, Team/TeamName ,Custom_BlockedStatus,State,ColumnName,LaneName,WorkItemID,ParentWorkItemID,WorkItemType,Title,CreatedDate,ActivatedDate,ResolvedDate,ClosedDate,ChangedDate,Custom_DefectSeverity,Custom_DIFRelease,Changedby/Username,CreatedBy/Username, AssignedTo/UserName,Area/AreaPath,Iteration/IterationPath),aggregate($count as Count))", null, [Implementation="2.0"]),
    #"Expanded Team" = Table.ExpandRecordColumn(Source, "Team", {"TeamName"}, {"Team.TeamName"}),
    #"Expanded Area" = Table.ExpandRecordColumn(#"Expanded Team", "Area", {"AreaPath"}, {"Area.AreaPath"}),
    #"Expanded AssignedTo" = Table.ExpandRecordColumn(#"Expanded Area", "AssignedTo", {"UserName"}, {"AssignedTo.UserName"}),
    #"Expanded ChangedBy" = Table.ExpandRecordColumn(#"Expanded AssignedTo", "ChangedBy", {"UserName"}, {"ChangedBy.UserName"}),
    #"Expanded CreatedBy" = Table.ExpandRecordColumn(#"Expanded ChangedBy", "CreatedBy", {"UserName"}, {"CreatedBy.UserName"}),
    #"Expanded Iteration" = Table.ExpandRecordColumn(#"Expanded CreatedBy", "Iteration", {"IterationPath"}, {"Iteration.IterationPath"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Iteration", "De-Duplication", each if [State] = "Completed" and
[DateValue] > Date.AddDays([ClosedDate],15) then "Exclude" else "Inculde"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"De-Duplication"] = "Inculde"))
in
    #"Filtered Rows"

最优方案：拉取上次查询后的新记录 #

核心思路：记录上次查询的最大ChangedDate，每次查询仅拉取该日期之后的增量数据，避免重复拉取

完整代码示例：

let
    // 步骤1：读取上次查询的截止日期（保存为本地CSV文件）
    LastRunDatePath = "C:\YourPath\LastRunDate.csv",
    LastRunDate = try DateTime.From(Table.FirstValue(Csv.Document(File.Contents(LastRunDatePath)))) otherwise #datetime(2024,1,1,0,0,0),
    LastRunDateText = Text.Format("yyyy-MM-ddTHH:mm:ssZ", LastRunDate),
    
    // 步骤2：拉取增量数据（ChangedDate >= 上次截止日期）
    Source = OData.Feed("https:......?$apply=filter((Team/TeamName eq 'DXC Scrum Team 1' or Team/TeamName eq 'DXC Scrum Team 2' ) and BoardName eq 'Evolve Requirements' and ChangedDate ge " & LastRunDateText & ")/groupby((DateValue, Team/TeamName ,Custom_BlockedStatus,State,ColumnName,LaneName,WorkItemID,ParentWorkItemID,WorkItemType,Title,CreatedDate,ActivatedDate,ResolvedDate,ClosedDate,ChangedDate,Custom_DefectSeverity,Custom_DIFRelease,Changedby/Username,CreatedBy/Username, AssignedTo/UserName,Area/AreaPath,Iteration/IterationPath),aggregate($count as Count))", null, [Implementation="2.0"]),
    #"Expanded Team" = Table.ExpandRecordColumn(Source, "Team", {"TeamName"}, {"Team.TeamName"}),
    #"Expanded Area" = Table.ExpandRecordColumn(#"Expanded Team", "Area", {"AreaPath"}, {"Area.AreaPath"}),
    #"Expanded AssignedTo" = Table.ExpandRecordColumn(#"Expanded Area", "AssignedTo", {"UserName"}, {"AssignedTo.UserName"}),
    #"Expanded ChangedBy" = Table.ExpandRecordColumn(#"Expanded AssignedTo", "ChangedBy", {"UserName"}, {"ChangedBy.UserName"}),
    #"Expanded CreatedBy" = Table.ExpandRecordColumn(#"Expanded ChangedBy", "CreatedBy", {"UserName"}, {"CreatedBy.UserName"}),
    #"Expanded Iteration" = Table.ExpandRecordColumn(#"Expanded CreatedBy", "Iteration", {"IterationPath"}, {"Iteration.IterationPath"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Iteration", "De-Duplication", each if [State] = "Completed" and
[DateValue] > Date.AddDays([ClosedDate],15) then "Exclude" else "Inculde"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"De-Duplication"] = "Inculde")),
    
    // 步骤3：追加到现有表（假设现有表在Excel中名为"ExistingData"）
    ExistingData = Excel.CurrentWorkbook(){[Name="ExistingData"]}[Content],
    CombinedData = Table.Combine({ExistingData, #"Filtered Rows"}),
    // 去重：按WorkItemID和ChangedDate确保不重复
    DeduplicatedData = Table.Distinct(CombinedData, {"WorkItemID", "ChangedDate"}),
    
    // 步骤4：更新并保存新的截止日期（取本次拉取数据的最大ChangedDate）
    NewLastRunDate = if Table.IsEmpty(DeduplicatedData) then LastRunDate else List.Max(DeduplicatedData[ChangedDate]),
    NewLastRunDateTable = Table.FromRecords({[LastRunDate = NewLastRunDate]}),
    SaveLastRunDate = Csv.Document(NewLastRunDateTable, [Delimiter=",", Encoding=1252])
in
    DeduplicatedData

注意事项 #

• 替换LastRunDatePath为实际保存路径，首次运行会自动创建文件
• 若现有表不在Excel中，可替换ExistingData的读取逻辑（比如从CSV/数据库读取）
• 优先用ChangedDate作为增量过滤条件，因为它能覆盖新增和修改的记录

追加数据到现有表的通用方法 #

• Excel用户：在Power Query编辑器中，选择「追加查询」→「追加两个表」，选择现有表和新拉取的表
• 自动去重：使用Table.Distinct函数，根据唯一标识字段（如WorkItemID+ChangedDate）去重

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