I am attempting to pull many (10,000+) api calls which each produce one record into a 10,000+ row table. The API has the following format in which it requires these records be pulled: BaseUrl/tickets/[id]
The id is a range of values from 5000 to an upper limit that I am able to define using a different call to the API. I have mostly been using the code from this article:
https://rbranger.wordpress.com/2021/01/02/jira-server-reporting-creating-the-issue-dimension-using-p...
The issue is that this code requires that the id (or page) is sequential, and in my case, say [id] = 6000 returns a 404 error, the entire workflow falls apart. I have tried debugging to determine where I can force the 404 to be ignored in the temp table but I keep coming up flat. My code is slightly altered from the link so I have posted it below:
load_issue table:
// Read all pages of data.
// After every page, we check the "NextLink" record on the metadata of the previous request.
// get_NextPage will keep asking for more pages until we return null.
let
newesttix = Json.Document(Web.Contents("https://url/tickets", [Headers=[#"Content-Type"="application/json", Authorization="Token" & token]])),
num_tix = newesttix{0}[friendly_id],
load_issue = get_NextPage((previous) =>
let
// if previous is null, then this is our first page of data
newStartAt = if (previous = null) then 5598 else Value.Metadata(previous)[NextLink]?,
// if NextLink was set to null by the previous call, we know we have no more data
page = if (newStartAt <> null) then get_IssueWithStartAt(newStartAt, num_tix) else null
in
page
)
in
load_issue
get_NextPage function:
// The getNextPage function takes a single argument and is expected to return a nullable table
(getNextPage as function) as table =>
let
listOfPages = List.Generate(
() => getNextPage(null), // get the first page of data
(lastPage) => lastPage <> null, // stop when the function returns null
(lastPage) => getNextPage(lastPage) // pass the previous page to the next function call
),
// concatenate the pages together
tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
firstRow = tableOfPages{0}?
in
// if we didn't get back any pages of data, return an empty table
// otherwise set the table type based on the columns of the first page
if (firstRow = null) then
Table.FromRows({})
else
Value.ReplaceType(
Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
Value.Type(firstRow[Column1])
)
get_IssueWithStartAt function:
(startAt as number, pages as number) as table =>
let
Quelle = Json.Document(Web.Contents(BaseUrl, [Headers=[#"Content-Type"="application/json", Authorization="Token " & token], RelativePath = Text.From(startAt)])),
nextLink = get_newStartAt(startAt, pages),
issues = Quelle,
#"Converted to Table" = Table.FromRecords({issues})
in
#"Converted to Table" meta [NextLink = nextLink]
get_NewStartAt function:
(lastStartAt as nullable number, total as nullable number) =>
let
#"newStartAt" = if lastStartAt + 1 > total then null else lastStartAt + 1
in
#"newStartAt"
Any help would be appreciated.
