Export Jira V3 api data to PowerBI dataflow

[JCMT]

Hi All, 

Trying to migrate an existing dataflow to use the Jira V3 api using the approach outlined in this blog and am having a bit of difficulty - 

I can connect to the api and get a single batch of results, but seem to be having issues iterating through all pages using the nextPageToken that's returned in each response. 

Below is the M code, this results in a list containing the same 100 tickets in each row, probably worth mentioning that for the last page of results the 'nextPageToken' field is omitted completely, hence using that as null as the exit condition from the loop. 

List.Generate( () =>
    [Result = Json.Document(Web.Contents("https://mycompanydomain.atlassian.net/rest/api/3/search/jql?jql=project=PROJ&maxResults=100&fields=*all&nextPageToken"))],
    each [Result][nextPageToken] <> null,
    each [Result = Json.Document(Web.Contents("https://mycompanydomain.atlassian.net/rest/api/3/search/jql?jql=project=PROJ&maxResults=100&fields=*all&nextPageToken" & [Result][nextPageToken]))]
)

 Any assistance / insights would be greatly apprechated. 

[Ahmed-Elfeel]

Hi @JCMT,
I noticed you concatenated With &nextPageToken directly with the token string and this is wrong you should put = sign between parameter and value.

the issue here is that the jira did not recognize it as a parameter.

So edit you code with

each [Result = Json.Document(
    Web.Contents("...&nextPageToken=" & [Result][nextPageToken])
)]

instead of:

each [Result = Json.Document(Web.Contents("...&nextPageToken" & [Result][nextPageToken]))]

 Let me know if it works for you :).

Best Regards.

[JCMT]

Hi @Ahmed-Elfeel, 

Ah, rookie syntax mistake there, thanks very much for highlighting it. 

It seems now at least when I run that code it's calling the API the correct number of times - the counter in the lower RHS gets to about 100 rows (there are approx 10k records to be downloaded), however the resulting list only contains a single 'Result' object as opposed to a list of ~100 'Result' objects. 

Unfortunately PowerQuery isn't offering much in the way of verbose feedback.. 

Regards, 

[Ahmed-Elfeel]

Hi @JCMT.,
ok the error here that power bi collect all code respone in one line not in the list or array..instead of collecting all records...it put Every page as an object (and this is wrong already).

You shoul tell Power query : hey! give me the array that has the data in every page and collect all of these data...So insttead of keeping the whole Response object....point directly to the array that has your rows...It can be [items] or [value] or [records] (based on your JSON code).

After that use (List.Combine) to flat all pages into onelist ( You can google it or share you JSON code and i will edit it to you if you want).

After all of these you will see +10k rows listed insted of 1 Object.

So in short: replace [Response] with [Response][items] (or the right property name) then combine all pages.

I hope you find this helpful :).

Best Regrads.

[JCMT · Accepted solution]

Hi @Ahmed-Elfeel ,

Yeah I knew procedurally what needed to be done, but familiarity with M is still developing. 

After a solid night's reading and a bit of trial and error here's the final (working) solution:

let
  // Fetch an individual page, nextPageToken is an optional paramater
  GetPage = (optional nextPageToken as text) =>
        let
            // Create URL, use nextPageToken if present
            Url = "https://mycompanydomain.atlassian.net",
            relativePath = if nextPageToken = null or nextPageToken = "" 
                  then "/rest/api/3/search/jql?jql=project=PROJ&maxResults=100&fields=*all" 
                  else "/rest/api/3/search/jql?jql=project=PROJ&maxResults=100&fields=*all&nextPageToken=" & nextPageToken,

            Response = Json.Document(Web.Contents(Url, [RelativePath = relativePath])),
            Issues = try Response[issues] otherwise {},
            NextToken = try Response[nextPageToken] otherwise null,
            Result = [Issues = Issues, NextToken = NextToken]
        in
            Result,
  // Recursive function to collate results from all pages
  GetAllPages = (nextPageToken as nullable text) as list =>
        let
            CurrentPage = GetPage(nextPageToken),
            CurrentIssues = CurrentPage[Issues],
            NextToken = CurrentPage[NextToken],
            MoreIssues = if NextToken <> null 
                         then @GetAllPages(NextToken) 
                         else {},
            Combined = List.Combine({CurrentIssues, MoreIssues})
        in
            Combined,
  // Call the recursive function starting with nextPageToken = null
  AllIssues = GetAllPages(null),

Appreciate your assistance though 🙂

Regards

[lbendlin]

Make sure this performs well enough. With recursive solutions (and List.Accumulate) you are lugging all the so far retrieved data around into each new iteration.  That can lead to memory shortage.  Better to use List.Generate and the browser cache.