Power query API connector multiple request

[macinrr]

Hi, 

I hit a wall and have no more ideas what should I do. I am doing a jira integration with Power Bi. 

I have created an All issues table which is supposed to fetch data from JIRA API.

Then I have multiple tables which reference All issues and expand only those parts they need.

I expected to see just one request to JIRA and everything else would just reuse that fetched data. I even tried with Buffer but when I investigated the traffic, this is how it looks like. It starts slowly, but with each pagination bigger I get more and more requests...

Here is my All issues table

let 
    JiraIdCount = List.Max({JiraIdPerPage, getIssuesCount(1) }),
    PageCount   = Number.RoundUp(JiraIdCount / JiraIdPerPage),
    PageIndices = if PageLimit <> -1 then { 0 .. PageLimit} else {0 .. PageCount - 1 },
    Pages = List.Transform(PageIndices, each List.Buffer(Function.InvokeAfter(()=>getIssues(_, true,1,"creator,assignee,created,customfield_10045,customfield_10054,issuetype,project,status,summary,priority,customfield_10060,customfield_10063,customfield_10073,customfield_10072,customfield_10076,customfield_10074,aggregatetimespent,resolution,resolutiondate,duedate,votes,customfield_10222,customfield_10019,customfield_10097,parent,versions,fixVersions,customfield_10053,customfield_10183,customfield_10059,customfield_10042,components"), #duration(0, 0, 0, 2)))),
    JiraID    = List.Union(Pages),
    Table       = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

Here is example Issues table which is using that All issues table

let
    Source = #"Staging - All issues",
    ExpandColumns = Table.ExpandRecordColumn(Source, "Column1", {"key", "fields"}, {"Key", "Fields"})
in
    ExpandColumns

The functions I used are

getIssuesCount

let
    Source = (filter as number) =>
        let 
            RawData = Web.Contents("https://xyz.atlassian.net",
            [
                RelativePath="rest/api/3/search",
                Query=
                [
                    jql=Text.Split(#"JIRA Issues", ";"){filter},
                    maxResults= "0"
                ]

            ]),
            Json    = Json.Document(RawData),
            IssueCount = Json[total]
        in  IssueCount
in
    Source

and getIssues

let
    Source = (_index as number, changelog as logical, filter as number, myfields as text) =>
        let 
            RawData = Binary.Buffer(Web.Contents("https://xyz.atlassian.net",
            [
                RelativePath="rest/api/3/search",
                Query=
                [
                    jql=Text.Split(#"JIRA Issues", ";"){filter},
                    startAt=Text.From(JiraIdPerPage*_index),
                    maxResults= Text.From(JiraIdPerPage),
                    expand = if changelog = true then "changelog" else "false",
                    fields = myfields
                ]

            ])),
            Json    = Json.Document(RawData),
            Issues  = Json[issues]
    in  Issues
in
    Source

As you will see, I tried using buffer in multiple various places... none of that blocked execution of the exact same request. Sounds like some racing condition, but not sure how this can be controlled (if it can at all). 

Any thoughts?

Thanks

Marcin

[macinrr]

@Joe_Barry Thanks for the example, but actually I have no problem integrating with jira. The problem is more generic, i.e. I keep seeing duplicated requests to API with the same content. 

Moreover, I see there are requests sent to the API, despite the fact I am updating tables (dates table to be more specific) which does not depend on any other table and is not a dependency to any other table...

Something is really strange

[Joe_Barry]

Hi @macinrr 

Jira is a pain to extract. Below is what I used for my previous company, hopefully it can help.

You will need to create a function first. open a Blank Query and enter this information

///Function///

(jql, fields, loop) =>
let
resultsPerPage = 500,
url = "YOURJIRAURL/",

jiraApiResult = Json.Document(Web.Contents(url, [RelativePath="rest/api/latest/search", Query=[startAt=Text.From(loop*resultsPerPage), maxResults=Text.From(resultsPerPage), fields=Text.From(fields), jql=Text.From(jql)]])),

result = if loop*resultsPerPage < jiraApiResult[total] 
then @getJiraIssue(jql, fields, loop+1)
else jiraApiResult[issues],

output = List.Buffer(List.Union({jiraApiResult[issues], result}))   

in
output

Then create a table, open blank query

///Table///

let
  jql="project in (YOURPROJECTNAME) AND issuetype IN (Incident) AND created > startOfYear(-5)",
  fields="THE, FIELDS, YOU, WANT, TO, EXTRACT",
  output = @getJiraIssue(jql, fields, 0),
    #"Converted to Table" = Table.FromList(output, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Ask the Jira admin for the Field names, if this isn't possible, open Jira in the Browser and right click on a field and inspect. It the new window, you need to look for possible filed names. There are some standard names and custom fields that should have the name customfield in it.

I hope this helps

Joe

If you found my answer helpful and it solved your issue, please accept as solution