I am new to PQ and I want to get data from REST API. The API uses pagination and in the 1st quary it returns two fileds. Table contains only 100 records.
IssueList = Table
NextBookmark = value
The url /issues?bookmark=[value] to get another page of issues. I am trying to use https://learn.microsoft.com/en-us/power-query/samples/trippin/1-odata/readme tutorial to create connector but fails to get values from another page.
Code below:
Table.GenerateByPage = (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])
);
GetAllPagesByNextLink = (url as text) as table =>
Table.GenerateByPage((previous) =>
let
// if previous is null, then this is our first page of data
nextLink = if (previous = null) then url else Value.Metadata(previous)[NextLink]?,
// if NextLink was set to null by the previous call, we know we have no more data
page = if (nextLink <> null) then GetPage(nextLink) else null
in
page
);
GetPage = (url as text) as table =>
let
response = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),
body = Json.Document(response),
nextLink = GetNextLink(body),
data = Table.FromRecords(body[IssueList])
in
data meta [NextLink = nextLink];
GetNextLink = (response) as nullable text => Record.FieldOrDefault(response[NextBookmark], "NextBookmark");
PQ.Feed = (url as text) as table => GetAllPagesByNextLink(url);
Can anyone help me out?
Thanks in advance for words of advice :)