'Power Query List Generate @odata.nextLink where pagination not available
I am trying to pull data from Dynamics CRM.
I am not able to use oData as this brings in the whole DB.= OData.Feed("https://crm/xxxxxx/api/data/v8.0/contacts")
Json allows the columns to be restricted but has a number of limits.
- 5000k limit
- No page number listed
- Offset function does not work
- Limit function does not work
- No total record count function
The below Json link returns two columns of information:
- Values column - A list of the first 5000 records
- oData.nextLink column - a link to the next 5000 records.
=Json.Document(Web.Contents("https://crm/xxxxxx/api/data/v8.0/contacts"))
I need to write function starting with the URL that returns the lists into a table. The first link is just a URL, the second is the oData link which makes coding harder. The loop ends on error.
I have tried too many methods to list, none give me the answer.
oData.nextLink is a large stringhttps://crm/xxxxxx/api/data/v8.0/contacts?$skiptoken=%3Ccookie%20pagenumber=%221%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%253e%253ccontactid%2520last%253d%2522%257bD0E5305F-0085-E211-9FD1-000C29854771%257d%2522%2520first%253d%2522%257b609AF16C-120C-4E2C-9498-00015D9B0068%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E
let
Source = {1..7},
BaseURL = "https://crm/xxxxxx/api/data/v8.0/contacts",
NextURL = Json.Document(Web.Contents("https://crm/xxxxxx/api/data/v8.0/contacts")) [#"@odata.nextLink"],
NextList = Json.Document(Web.Contents("https://crm/xxxxxx/api/data/v8.0/contacts")) [#"value"],
ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Renamed = Table.RenameColumns(ToTable,{{"Column1", "Page"}}),
AddedBase = Table.AddColumn(Renamed, "Base" as text, each BaseURL),
AddedLink = Table.AddColumn(AddedBase, "Next" as text, each NextURL),
AddedList = Table.AddColumn(AddedLink, "List" as text, each NextList)
in AddedList
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
