Forum Discussion
Power BI Service: JQL fails Jira API connection
I have made a connection to the Jira server and as long as I do not include the JQL the connection works fine, though out of the 150K records on the server I am only able to pull 1,800. With the query I have written I expect ~5K records. With the JQL included in the URL I get the following error: "Web.Contents failed to get contents from 'https://jiraserver.com/jira/rest/api/latest/search?"
In summary, my problem is twofold: 1) I need the JQL in order to reduce the population I am pulling, and 2) I am not able to pull the full population.
Anyone know what I am missing? Thanks!
(here is the M script I am working with)
let
BaseUrl = "https://jiraserver.com/jira/rest/api/latest/search?",
JiraIDPerPage = 1000,
JiraFields = "&fields=jira fields",
Jql = "jql=project in (PROJECTS)",
GetJson = (Url) =>
let RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetJiraIDCount = () =>
let Url = BaseUrl & "&maxResults=0",
Json = GetJson(Url),
Count = Json[#"total"]
in Count,
GetPage = (Index) =>
let Skip = "&startAt=" & Text.From(Index * JiraIDPerPage),
Top = "&maxResults=" & Text.From(JiraIDPerPage),
Url = BaseUrl & Jql & Skip & Top & JiraFields,
Json = GetJson(Url),
Value = Json[#"issues"]
in Value,
JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
PageCount = Number.RoundUp(JiraIDCount / JiraIDPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
JiraID = List.Union(Pages),
Table = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"})
in
#"Expanded Column1"
1 Reply
- MitchellRegular Visitor
Okay figured out the way to fix the limited population was to reduce the JiraIDPerPage variable to 100. Still stuck on why the JQL is causing an error.