PowerBI M查询JIRA工单陷入无限循环问题求助

安全阿岳

2026-6-11

解决PowerBI M查询JIRA数据的无限循环问题

我编写了一段PowerBI M查询用于获取204条JIRA工单数据，但查询陷入无限循环，反复返回相同结果。尝试过多种修改方案均无效，恳请协助解决。

let
    //--- Basic Info ---
    BaseUrl = "https://testaccount.atlassian.net/rest/api/3/search/jql?",
    JQL = "jql=dueDate < now() and status NOT IN (Done, Backlog, Complete, ""Closed/Complete"", ""Closed/Completed"", ""Not Required"", Closed, Launched, Descoped, ""Won't Do"", Approved, Validated)&fields=summary,duedate,status,assignee,issuetype,project ORDER BY status DESC",
    PageSize = 100,

    // --- Function to Get a Page ---
    GetPage = (StartAt as number) =>
    let
        Url =
            BaseUrl & JQL &
            "&fields=*all" &
            "&startAt=" & Text.From(StartAt) &
            "&maxResults=" & Text.From(PageSize),
        Response = Json.Document(Web.Contents(Url)),
        Issues = Record.FieldOrDefault(Response, "issues", {}),
        Total = Record.FieldOrDefault(Response, "total", 0)
    in
        [Issues = Issues, Total = Total],

    // --- Loop Over All Pages ---
    AllPages = List.Generate(
        () => [StartAt = 0, Page = GetPage(0)],
        each [StartAt] < [Page][Total],
        each
            let
                NewStartAt = [StartAt] + PageSize
            in
                [
                    StartAt = NewStartAt,
                    Page = GetPage(NewStartAt)
                ],
        each [Page][Issues]
    ),
    
    // --- Flatten Issues List ---
    Flattened = List.Combine(AllPages),
    IssuesTable = Table.FromList(Flattened, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(IssuesTable, "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", "Column1.key", "Column1.fields"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"statuscategorychangedate", "issuetype", "parent", "components", "timespent", "timeoriginalestimate", "project", "description", "fixVersions", "aggregatetimespent", "statusCategory", "resolution", "customfield_10015", "security", "aggregatetimeestimate", "customfield_10506", "resolutiondate", "customfield_10507", "customfield_10508", "workratio", "customfield_10509", "summary", "watches", "lastViewed", "creator", "subtasks", "created", "customfield_10020", "customfield_10021", "reporter", "aggregateprogress", "priority", "customfield_10001", "labels", "customfield_10016", "environment", "customfield_10019", "timeestimate", "aggregatetimeoriginalestimate", "versions", "duedate", "progress", "issuelinks", "votes", "assignee", "updated", "status", "customfield_10036", "customfield_10109"}, {"Column1.fields.statuscategorychangedate", "Column1.fields.issuetype", "Column1.fields.parent", "Column1.fields.components", "Column1.fields.timespent", "Column1.fields.timeoriginalestimate", "Column1.fields.project", "Column1.fields.description", "Column1.fields.fixVersions", "Column1.fields.aggregatetimespent", "Column1.fields.statusCategory", "Column1.fields.resolution", "Column1.fields.customfield_10015", "Column1.fields.security", "Column1.fields.aggregatetimeestimate", "Column1.fields.customfield_10506", "Column1.fields.resolutiondate", "Column1.fields.customfield_10507", "Column1.fields.customfield_10508", "Column1.fields.workratio", "Column1.fields.customfield_10509", "Column1.fields.summary", "Column1.fields.watches", "Column1.fields.lastViewed", "Column1.fields.creator", "Column1.fields.subtasks", "Column1.fields.created", "Column1.fields.customfield_10020", "Column1.fields.customfield_10021", "Column1.fields.reporter", "Column1.fields.aggregateprogress", "Column1.fields.priority", "Column1.fields.customfield_10001", "Column1.fields.labels", "Column1.fields.customfield_10016", "Column1.fields.environment", "Column1.fields.customfield_10019", "Column1.fields.timeestimate", "Column1.fields.aggregatetimeoriginalestimate", "Column1.fields.versions", "Column1.fields.duedate", "Column1.fields.progress", "Column1.fields.issuelinks", "Column1.fields.votes", "Column1.fields.assignee", "Column1.fields.updated", "Column1.fields.status", "Column1.fields.customfield_10036", "Column1.fields.customfield_10109"}),
    #"Expanded Column1.fields.issuetype" = Table.ExpandRecordColumn(#"Expanded Column1.fields", "Column1.fields.issuetype", {"self", "id", "description", "iconUrl", "name", "subtask", "avatarId", "entityId", "hierarchyLevel"}, {"Column1.fields.issuetype.self", "Column1.fields.issuetype.id", "Column1.fields.issuetype.description", "Column1.fields.issuetype.iconUrl", "Column1.fields.issuetype.name", "Column1.fields.issuetype.subtask", "Column1.fields.issuetype.avatarId", "Column1.fields.issuetype.entityId", "Column1.fields.issuetype.hierarchyLevel"}),
    #"Expanded Column1.fields.project" = Table.ExpandRecordColumn(#"Expanded Column1.fields.issuetype", "Column1.fields.project", {"name"}, {"Column1.fields.project.name"}),
    #"Expanded Column1.fields.reporter" = Table.ExpandRecordColumn(#"Expanded Column1.fields.project", "Column1.fields.reporter", {"displayName"}, {"Column1.fields.reporter.displayName"}),
    #"Expanded Column1.fields.status" = Table.ExpandRecordColumn(#"Expanded Column1.fields.reporter", "Column1.fields.status", {"self", "description", "iconUrl", "name", "id", "statusCategory"}, {"Column1.fields.status.self", "Column1.fields.status.description", "Column1.fields.status.iconUrl", "Column1.fields.status.name", "Column1.fields.status.id", "Column1.fields.status.statusCategory"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.fields.status",{"Column1.expand", "Column1.id", "Column1.self", "Column1.fields.statuscategorychangedate", "Column1.fields.issuetype.self", "Column1.fields.issuetype.id", "Column1.fields.issuetype.description", "Column1.fields.issuetype.iconUrl", "Column1.fields.issuetype.subtask", "Column1.fields.issuetype.avatarId", "Column1.fields.issuetype.entityId", "Column1.fields.issuetype.hierarchyLevel", "Column1.fields.parent", "Column1.fields.components", "Column1.fields.timespent", "Column1.fields.timeoriginalestimate"}),
    #"Expanded Column1.fields.assignee" = Table.ExpandRecordColumn(#"Removed Columns", "Column1.fields.assignee", {"displayName"}, {"Column1.fields.assignee.displayName"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Column1.fields.assignee",{"Column1.fields.status.id", "Column1.fields.status.statusCategory", "Column1.fields.customfield_10036", "Column1.fields.customfield_10109", "Column1.fields.updated", "Column1.fields.status.self", "Column1.fields.status.description", "Column1.fields.status.iconUrl", "Column1.fields.progress", "Column1.fields.issuelinks", "Column1.fields.votes", "Column1.fields.watches", "Column1.fields.lastViewed", "Column1.fields.creator", "Column1.fields.subtasks", "Column1.fields.created", "Column1.fields.customfield_10020", "Column1.fields.customfield_10021", "Column1.fields.reporter.displayName", "Column1.fields.aggregateprogress", "Column1.fields.priority", "Column1.fields.customfield_10001", "Column1.fields.labels", "Column1.fields.customfield_10016", "Column1.fields.environment", "Column1.fields.customfield_10019", "Column1.fields.timeestimate", "Column1.fields.aggregatetimeoriginalestimate", "Column1.fields.versions", "Column1.fields.description", "Column1.fields.fixVersions", "Column1.fields.aggregatetimespent", "Column1.fields.statusCategory", "Column1.fields.resolution", "Column1.fields.customfield_10015", "Column1.fields.security", "Column1.fields.aggregatetimeestimate", "Column1.fields.customfield_10506", "Column1.fields.resolutiondate", "Column1.fields.customfield_10507", "Column1.fields.customfield_10508", "Column1.fields.workratio", "Column1.fields.customfield_10509"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1.key", "Issue ID"}, {"Column1.fields.issuetype.name", "Issue Type"}, {"Column1.fields.project.name", "Project Name"}, {"Column1.fields.summary", "Summary"}, {"Column1.fields.duedate", "Due Date"}, {"Column1.fields.assignee.displayName", "Assignee"}, {"Column1.fields.status.name", "Status"}})
in
    #"Renamed Columns"

问题分析

原查询陷入无限循环的核心原因有两点：

分页迭代逻辑错误：在List.Generate的迭代步骤中，调用GetPage时使用的是旧的StartAt值加上PageSize，而非更新后的StartAt，导致后续请求的分页参数计算错误，反复请求同一页。
终止条件不可靠：仅通过当前页的工单数量判断是否继续循环，若JIRA API返回空列表的逻辑不符合预期，会导致循环无法终止。

修改说明

增强GetPage函数：从JIRA响应中获取total字段（总工单数量），用于精准控制循环终止时机。
修正迭代逻辑：通过临时变量NewStartAt先计算新的分页起始位置，再用该值调用GetPage，确保每次请求的分页参数正确。
优化终止条件：改用StartAt < Total作为循环终止判断，当起始位置超过总工单数量时自动停止循环，彻底避免无限循环问题。

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

PowerBI M查询JIRA工单陷入无限循环问题求助

问题分析 #

修改说明 #

问题分析

修改说明