Jira API's and power BI

[Graham Boag]

Hi everyone,

I’ve been using Jira since the start of the year and have done quite a lot with it, and it’s been great. More recently, I set up Power BI to pull data from Jira, which was also working really well.

I’ll be honest — I have used AI to help with some of the queries, but since the recent Jira 10 changes, my setup has completely stopped working. Refreshes are no longer occurring, and nothing seems to return data.

I’ve removed the token and site name from the snippet below for security, but otherwise this is exactly what I’m running. The query executes without errors, but no issues are returning, even when I make the JQL very general.

I’ve tried adjusting the query in various ways, but no matter what I do, I can’t seem to get any data back. Any guidance on what might be missing with Jira 10 or how to fix this would be hugely appreciated.

let
// Function to fetch a page of issues
FetchPage = (StartAt as number) =>
let
url = "https://mysite.atlassian.net/rest/api/3/search/jql",
body = "{""jql"":""project=CLEAR ORDER BY created DESC"",""startAt"":" & Text.From(StartAt) & ",""maxResults"":100,""fields"":[""*all*""]}",
SourceRaw = Web.Contents(url,
[
Content = Text.ToBinary(body, TextEncoding.Utf8),
Headers =
[
Authorization = "Basic my token",
#"Content-Type" = "application/json; charset=utf-8",
Accept = "application/json"
],
ManualStatusHandling = {400,401,403,404,410}
]
),
SourceJSON = try Json.Document(SourceRaw) otherwise null
in
SourceJSON,

// Fetch first page to get total
FirstPage = FetchPage(0),
Total = if FirstPage <> null and Record.HasFields(FirstPage,"total") then FirstPage[total] else 0,
PageSize = 100,
PageCount = Number.RoundUp(Total / PageSize),

// Generate startAt values
StartAts = List.Transform({0..PageCount-1}, each _ * PageSize),

// Fetch all pages
AllPages = List.Transform(StartAts, each FetchPage(_)),

// Combine issues from all pages
AllIssues = List.Combine(List.Transform(AllPages, each if _ <> null and Record.HasFields(_, "issues") then _[issues] else {})),

// Turn into table
IssuesTable = Table.FromList(AllIssues, Splitter.SplitByNothing(), {"issues"}),

ExpandedRecords = Table.ExpandRecordColumn(
IssuesTable,
"issues",
{"id", "key", "fields"},
{"IssueID", "Key", "fields"}
),

// Expand specific fields
ExpandedFields = Table.ExpandRecordColumn(
ExpandedRecords,
"fields",
{
"issuetype","summary","status","parent","assignee","duedate",
"customfield_10238","customfield_10896","customfield_10230","customfield_10891",
"customfield_10015","customfield_10602","customfield_10740","customfield_10903",
"customfield_10905","customfield_10906"
},
{
"Issue Type Record","Summary","Status Record","Parent Record","Assignee Record",
"Due Date","Percentage Complete","Initial Construction Footage","Total Construction Distance",
"Passings","Start Date","Submitted Date","Signoff Date","Days on Hold","Days in Status","Errors"
}
),

ExpandedIssueType = Table.ExpandRecordColumn(ExpandedFields, "Issue Type Record", {"name"}, {"Issue Type"}),
ExpandedStatus = Table.ExpandRecordColumn(ExpandedIssueType, "Status Record", {"name"}, {"Status"}),
ExpandedParent = Table.ExpandRecordColumn(ExpandedStatus, "Parent Record", {"key"}, {"Parent"}),
ExpandedAssignee = Table.ExpandRecordColumn(ExpandedParent, "Assignee Record", {"displayName"}, {"Assignee"}),

WithProject = Table.AddColumn(ExpandedAssignee, "Project", each "CLEAR"),

ChangedTypes = Table.TransformColumnTypes(
WithProject,
{
{"Issue Type", type text}, {"Summary", type text}, {"Status", type text}, {"Parent", type text},
{"Assignee", type text}, {"Due Date", type date}, {"Start Date", type date}, {"Submitted Date", type date},
{"Signoff Date", type date}, {"Percentage Complete", type number},
{"Initial Construction Footage", Int64.Type}, {"Total Construction Distance", Int64.Type},
{"Passings", Int64.Type}, {"Days on Hold", Int64.Type}, {"Days in Status", Int64.Type},
{"Errors", Int64.Type}, {"Project", type text}
}
)
in
ChangedTypes