Power Query connector - pagination issue

Viewed 30

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 :)

0 Answers
Related