How to store more than 1000 rows in a query

[Aman-K]

Hi All,

I have done Power BI and Jira integration using Rest API's. There are around 1400 issues in Jira but in my Power query it only shows 1000 rows . I tried various ways to increase the limit but no joy. Can anyone please help me with storing all data from Jira in Power BI ?Please see below my query so far. 

let
Source = Json.Document(Web.Contents("https://jira/rest/api/2/search?jql=project%20in%200)&startAt=0&maxResults=1000", [Headers=[Authorization="Bearer Nzk5MTE4NDU3NTM5Ovc2WYFJpdc9acLGmD"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded issues" = Table.ExpandListColumn(#"Converted to Table", "issues"),
#"Expanded issues1" = Table.ExpandRecordColumn(#"Expanded issues", "issues", {"expand", "id", "self", "key", "fields"}, {"issues.expand", "issues.id", "issues.self", "issues.key", "issues.fields"}),

Thanks so much .

Aman

[ppm1]

Please see this video. It walks through how to handle this scenario by first creating a list/table of numbers (1, 1001, 2002, etc.), convert it to text, and then concatenate those for your startAt values into a web call on each row.

Power BI - Tales From The Front - REST APIs - YouTube

Pat

[Aman-K]

Hi @ppm1 , Thanks for sharing the video link. I'm afraid I am still stuck at this . 

I have read about pagination and how we can get 100 rows at a time per page by looping through the records but I am not getting anywhere with this . 

I have about 1400 issues in Jira but at the moment I can only get 1000 records . I have tried different ways but can't get around with this.  I would really appreciate if someone can share the solution with me. Thanks 

[teenarose2003]

It should be as below..

StartAt=1001... Hopefully it works!!

[Aman-K]

Hi @teenarose2003 

Do you mean creating a new query and starting at 1001 row? I tried that as well and got 2 queries , query 1 with first 1000 rows and second one with rest of the rows but when I am creating reports then how can I get data from both the queries ? I tried merging 2 queries into 1 but again get the same issue with the limit on number of rows . 

Thanks

[Migasuke]

HI @Aman-K ,

Maybe a silly question, but what about the url parameter you have maxResults=1000 ?
I would try to increase it.

[Aman-K]

Hi @Migasuke 

I have tried to increase it but it doesn't work. I get the error message "Evaluation resulted in Stack Overflow and cannot continue". There is a limit in the number of rows we can store in a query i.e1000. 

Hope to hear a solution for this in the forum .

Thanks

Aman

[Migasuke]

Based on the forum of JIRA, you can't have more than 1000 (unless you probably scale up with your developer tier)

https://community.atlassian.com/t5/Jira-questions/How-to-Increase-REST-API-Index-search-limit/qaq-p/...

I guess you need to create two queries. One will download first half, second which takes the other half.
To prevent API overload, try to implement in second query some wait/delay statement. It's not very easy in PQ, but there are some options:
https://learn.microsoft.com/en-us/power-query/wait-retry

[Aman-K]

I have 2 queries already, one has data from 1 to 1000 and the second has data from 1001 to 2000. How can I use data from both teh queries in my reports in Power BI desktop?

Any help will be much appreciated!

Many Thanks,

Aman

[Migasuke]

Hi @Aman-K ,

Append those queries together. You should find "Append" in main menu in Power Query Editor.

https://learn.microsoft.com/en-us/power-query/append-queries