Pagination special character

[ClAgi]

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 

Best regards !

[Jonas1]

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]

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]

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

[v-huizhn-msft]

Hi @ClAgi,

After research, there are some similar thread for your reference. And I personally suggest you post your thread to Power Query forum to get professional support.

Unable to convert a value of type List in the Record Type in Query
Convert text to number

Best Regards,
Angelia