'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.

  1. 5000k limit
  2. No page number listed
  3. Offset function does not work
  4. Limit function does not work
  5. No total record count function

The below Json link returns two columns of information:

  1. Values column - A list of the first 5000 records
  2. 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 string
https://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