Forum Discussion
gotmike
10 years agoFrequent Visitor
how to create a query that paginates?
I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the resu...
ImkeF
Community Champion
7 years agoYou have to look it up in the API-documentation. Try a search for "pagination" there.
dathompson
7 years agoFrequent Visitor
I created a query with pagination to retrieve issues from GitHub, following Mark Tiedemann's helpful post here. I thought I'd share for anyone else who may be needing to report on GitHub issues.
Here's my code:
let
GitHubAPI = "https://api.github.com/search/issues?",
Repository = "q=repo:OwnerName/RepositoryName+",
SearchCriteria = "type:issue+state:open&",
BaseUrl = GitHubAPI & Repository & SearchCriteria,
EntitiesPerPage = 100,
GetJson = (Url) =>
let RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl,
Json = GetJson(Url),
Count = Json[#"total_count"]
in Count,
GetPage = (Index) =>
let PerPage = "per_page=" & Text.From(EntitiesPerPage),
Page = "page=" & Text.From(Index + 1),
Url = BaseUrl & PerPage & "&" & Page,
Json = GetJson(Url),
Value = Json[#"items"]
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount -1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in Table