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
ImkeF's avatar
ImkeF
Community Champion
8 years ago

Have you refreshed the query? I've used a filter which I might have forgotten to take out.

For learning purposes I'd suggest that you step through the queries: Pretty sure you find the filters (just delete them) and the answer to your URL question in there ;)

julianchen's avatar
julianchen
Advocate I
8 years ago

After removing the filter as you alerted, I also modified the Parameter Table_Rows query as shown below (starting from page = 0, and taking out if clause. Finally, I got 19,561 rows - excess 24 records of gap. I will check technicalIndex one after one to dig out what's the gap if possible. That's the learning purposes as you guided me. Thanks again for your great help. I've been expecting to have this "homework" to be done for quite a long time. 

 

let
    Source = #"Parameter Table",
    #"Added Custom" = Table.AddColumn(Source, "Pages", each {0..[Page]}),
    #"Expanded Pages" = Table.ExpandListColumn(#"Added Custom", "Pages"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Pages", "Custom", each fnGetPages([TechnicalIndex], Text.From([Pages]))),
 // #"Added Custom1" = Table.AddColumn(#"Expanded Pages", "Custom", each if [Pages] = 1 then fnGetPages([TechnicalIndex], null) else fnGetPages([TechnicalIndex], Text.From([Pages]))),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom1", {"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Errors", "Custom", {"Column1", "Term", "Link", "Index"}, {"Column1", "Term", "Link", "Index.1"}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Expanded Custom", "Text After Delimiter", each Text.AfterDelimiter([Term], "/", 2), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Term", "Term_1"}, {"Text After Delimiter", "Term"}})
in
    #"Renamed Columns"

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi all,

     

    I have been struggling to paginate JIRA's rest API where the max results can be obtained for only 1000 rows. The pagination technique in this page so far provides a custom code which is really easy in an ideal scenario for a given scenario but when there is no ideal scenario or use different source with custom code which was my scenario and I stumbled on a very easy and an intiutive manner in which it was explained using Invoke Function out of the box instead of creating custom codes. Please refer to this page for more information and this helped to resolve the issue.

     

    https://stackoverflow.com/questions/46904641/play-with-paginated-data-with-power-bi

     

    Kind regards,

    A!