Power Query with Pagination

When dealing with larger data sets, it will be necessary to use pagination and the skip and take variables within some of the endpoints.

Please see below an example of how to recursively leverage the NextPageLink within the response to get batches of results, which are then combined for use in Power BI.

fnCxDocumentGetByDocCode = (cxApi as text, cxProjectName as text, cxKey as text, formCode as text) =>
  let
    headers = [ Headers = [#"key" = cxKey, #"Content-Type" = "application/json"] ],
    firstPage = Json.Document(Web.Contents(cxApi & "/Api/" & cxProjectName &"/Document/GetByDocCode?code=" & formCode & "&take=250", headers)),
    getAllPages = (allItems as list, uri) =>
      let
        nextPage = Json.Document(Web.Contents(uri, headers)),
        pageItems = nextPage[Items],
        allItems = List.Combine({ allItems, pageItems }),
        check = if nextPage[NextPageLink] = null then allItems else @getAllPages(allItems, nextPage[NextPageLink])
      in check,
      allPages = if firstPage[NextPageLink] = null then firstPage[Items] else getAllPages(firstPage[Items], firstPage[NextPageLink])                
    in allPages,

This function can then be called with:

documents = fnCxDocumentGetByDocCode(cxApi, cxProjectName, cxKey, "DEF"),
cxTable = Table.FromList(documents, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
cxResult = Table.ExpandRecordColumn(cxTable, "Column1", {"Id", "DocCode", "DocTypeCode", "Reference", "ProjectName", "StatusName", "StatusType", "Title", "AuthorCode", "AuthorCompanyCode", "CreatedDate", "IssuedDate", "DueDate", "ClosedDate", "LastClosedDate", "OpenedDate", "LastChangedDate", "LastModifiedDate", "FirstActionCompanyCode", "ActionCodes", "InfoCodes", "UserfieldValues", "Attachments", "Comments", "DocSettingId", "StatusId", "Timestamp", "IsPrivate", "LinkedDocuments"}),

For any data sources returning more than 1000 results, it is recommended to schedule their refresh to occur overnight outside of business hours.

The right page size to set with take = X will be dependent upon the complexity of the form’s configuration and the amount of data being returned per forms.

The larger the dataset the smaller the recommended page size.

As a starting point, we would suggest:

Forms with no list sections and up to 20 user fields: 250 per page.
Forms with a list section or a higher amount of user fields: 100 per page.

Each project has a defined maximum page size, which will be checked and returned as a Bad Request (400) error code if exceeded.