Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ClAgi
Frequent Visitor

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.

nextProblem.PNG

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

 

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 Smiley Frustrated

Best regards !

4 REPLIES 4
Jonas1
Helper I
Helper I

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!

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

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

v-huizhn-msft
Employee
Employee

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.