Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 !
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |