Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mitchell
Regular Visitor

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 1
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.