Forum Discussion
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
- v-huizhn-msft
Microsoft 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 - CramNew 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
Helper I
Ok, yeah hard to fix it seems. Thanks for replying.