Forum Discussion

ClAgi's avatar
ClAgi
Frequent Visitor
8 years ago

Pagination special character

I've spent almost 3 days on the problem but couldn't solve this by my own.

I build the pagination after this tutorial GitHub.

 

My main problem is that the element in the body, which i want to access, has a special character "$" in front of the name. It's named "$next".

 

I can't take

 

data = Table.FromRecords(body[$next])

cause there is an error cause of the "$".

 

 

This is the answer of the webservice.

I think it's one record cause i can transform it with Record.ToTable() to a table which looks like this

 

I've tried to delete all "$" characters with

CleanColumns = (table) as table =>
    let
        result = Table.RenameColumns(table, Table.ToRows(Table.AddColumn(Table.FromList(Table.ColumnNames(table)), 
        "New Column Name", each Text.Replace ( [Column1] , "$" , "" ))))
    in
        result;

but i ended up with an error which said "couldn't convert table to record" and much much more errors.

 

 

Other Code:

 

Page = (url as text) as nullable table =>
    let
        response = Web.Contents(url, [ Headers = BuildHeader()]),
        body = Json.Document(response),
        nextLink = GetNextLink(body),
        data = Table.FromRecords(body[next])
    in
        data meta [next = nextLink];
GetNextLink = (response) as nullable text => Record.FieldOrDefault(response, "$next");

 

 

 

Rest of the code (same as in the tutorial):

 

 

PagedReader = (url as text) =>
    Table.GenerateByPage((previous) => 
        let
            // if previous is null, then this is our first page of data
            nextPage = if (previous = null) then url else Value.Metadata(previous)[next]?,
            // if NextPage was set to null by the previous call, we know we have no more data
            page = if (nextPage <> null) then Page(api_base_uri & nextPage & expand_all) else null
        in
            page
    );

 

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])
            );

 

 I hope somebody can help me out cause this i driving me crazy :smileyfrustrated:

Best regards !

4 Replies

  • Hi, 

     

    Currentely working with pagination in APIs as you did ClAgi . 

     

    Did you manage to fix your problem? And were your code correct?

     

    Really new to custom connectors and not so experienced with programming either, so everything that can help me on the way is a big help!

    • Cram's avatar
      Cram
      New Member

      Hi ClAgi here with another account.
      I ended up contacting the API developers and they increased the possible pagesize to 100k.
      That's no problem for the connector cause it needs all the data.
      Sorry that i can't help you with the pagination problem.
      Best regards

      • Jonas1's avatar
        Jonas1
        Icon for Helper I rankHelper I

        Ok, yeah hard to fix it seems. Thanks for replying.