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

[Anonymous]

Hello @macinrr, 

Would you consider trying an alternative approach? You may connect your Jira on-premise to Power BI with the help of our app - Power BI Connector for Jira: https://marketplace.atlassian.com/apps/1221150/power-bi-connector-for-jira?hosting=cloud&tab=overvie...

With our Power BI Connector you will be able to:

- build reports based on filtering of Jira custom fields and Marketplace apps custom fields
- combine reporting on multiple Jira projects
- use different data sources in single analytics (Jira, CRM, ERP, ITSM, Financial management software, etc)
- create Jira reports in Power BI with built-in Power BI dashboard templates

- scheduled refreshes for actual Jira reporting.

Daria

===
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[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