Forum Discussion

gotmike's avatar
gotmike
Frequent Visitor
10 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

hi Imke,

I am working on similar req like the ones in this post. i am new to coding. I need to pull data from web api which has limit of 2000 rows. but I need help to pull all the records.

this is what the query looks like

 

let
Source = Json.Document(Web.Contents("https://infotech.attask-ondemand.com/attask/api/v9.0/user/search?fields=*&apiKey=t95jkkcqimdgkkchkjsnwh&method=get")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"latestUpdateNoteID", "layoutTemplateID", "licenseType", "locale", "logTimeInDays", "loginCount", "managerID", "mobilePhoneNumber", "myInfo", "passwordDate", "persona", "phoneExtension", "phoneNumber", "Column1.hasDocuments", "Column1.hasNotes", "Column1.hasProofLicense", "Column1.hasReservedTimes", "Column1.homeGroupID", "Column1.homeTeamID", "Column1.isActive"})
in
#"Expanded Column1"

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
8 years ago

In general you do this by dynamically creating the URLs with page parameters who will then be called.

Therefor you need a URL-structure where you can integrate the row numbers to pull. 

Is this returned in the first result by any chance?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    yes, with above url gives 100 rows by default.It is  a workfront application url and I need to pull all rows from all pages.

    So you are saying, that url needs to have some page information like parameters? i only have that url right now. Can you suggest what the next steps be?

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    8 years ago

    You have to look it up in the API-documentation. Try a search for "pagination" there.

  • dathompson's avatar
    dathompson
    Frequent Visitor
    8 years ago

    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