Forum Discussion

Mitchell's avatar
Mitchell
Regular Visitor
3 years ago

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

  • Mitchell's avatar
    Mitchell
    Regular 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.