Pulling JIRA & Tempo data via REST API | Looking for Efficiencies

[Birkley]

Hi All,

I'm trying to Query our JIRA instance and pull in all issues that match a certain JQL string -- returning a list of custom fields. I'm then trying to return all the worklogs for those particular issues in a separate table.  I've managed to frankenstein together a solution after pouring through hundreds of posts, but I'm afraid the solution I have isn't exactly the most efficient.  I'm hoping one of you beautiful, helpful folks could help me maybe cleanup this query a bit to make it more efficent. 

JIRA Issue Data:

let 
    BaseUrl = "https://companyurl.com/rest/api/2/search?jql=project='something'&fields=customfield_11040,project,issuekey,summary,status,assignee,reporter,created,updated,customfield_17502",
    JiraIDPerPage = 1000,
 
    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url,[Headers=[Authorization="Bearer XXXXXXXXXXXXXXXXXXX"]]),
            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 & Skip & Top,
            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),
    #"Converted to Table" = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

=============REMOVING A BUNCH OF DATA EXPANDING/SHAPING HERE===================

in
    #"Converted to Table"

and JIRA Worklogs:

let 
S1 = #"Converted to Table",
    #"Add worklog url" = Table.AddColumn(S1, "worklog url", each [issue_api]&"/worklog"),
    #"Invoked Custom Function1" = Table.AddColumn(#"Add worklog url", "worklog", each Json.Document(Web.Contents([worklog url],[Headers=[Authorization="Bearer XXXXXXXXXXXXXXX"]])))

=============================BUNCH OF EXPANDING/SHAPING=========================

in
    #"Invoked Custom Function1"

Thank you so much in advance!

[Anonymous · Accepted solution]

HI @Birkley,

In fact, I think this code is well enough to handle the pagination rest API.

If you want to find methods wiht better performance, I will recommend you getting data and handle these rest API processing out of power bi. (create a service or API to getting pagination data and return merged result table)Then you only need to get the processed result data to design report on power bi side. (they should have better performances than processed on power query side)

Regards,

Xiaoxin Sheng

[Birkley]

I hope I've submitted my request correctly.  I'm a newbie, so please let me know if there is anything I should be doing different or providing.  Thanks again for any and all help!