Forum Discussion
Export Jira V3 api data to PowerBI dataflow
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
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.
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,
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.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
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.
