PowerBi query for JIRA tickets giving results in loop
I have this powerbi M query to find all the 204 JIRA tickets but I am getting same results again and again in infinte loop. I tried many combinations via ChatGPT and other site but no luck. Appreciate if anyone can help.
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", {})
in
[Issues = Issues],
// --- Loop Over All Pages ---
AllPages = List.Generate(
() => [StartAt = 0, Page = GetPage(0)],
each List.Count([Page][Issues]) > 0,
each
[
StartAt = [StartAt] + PageSize,
Page = GetPage([StartAt] + PageSize)
],
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"