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
tempranello's avatar
tempranello
Advocate I
10 years ago

EDIT:  I posted this to the wrong thread(!).  I should have replied here, and I'll continue and close the thread there.

 

Thanks ImkeF

 

My apologies for taking so long to acknowledge your support.  I've finally got around to having a go at List.Generate() and I reckon that I'm on the right path.  However, I think that I've created an infinite loop and thus launch a small-scale DoS attack against the Web API I'm trying to hit :-) 

 

Would you mind taking a look at my query text and giving me your opinion?  When I run it, I receive no errors, but the query appears to run forever.  At this point all that I'm trying to do is generate a list of URIs where I supply the first, and generate the rest based on the initial return.

 

let
    URIList = List.Generate(
    ()=> [SourceURI="https://api.dovico.com/TimeEntries/?version=5"],
    each Text.PositionOf([SourceURI],"N/A") = -1,
    each [
        Source = Web.Contents([SourceURI],[Headers=[#"Authorization"="WRAP access_token=""client=XXX&user_token=YYY"""]]),
        ImportedXML = Xml.Tables(Source,null,1252),
        ChangedType = Table.TransformColumnTypes(ImportedXML,{{"PrevPageURI", type text

  • ImkeF's avatar
    ImkeF
    Community Champion
    10 years ago

    I cannot spot anything obvious.

    Have never used it without the counter, but cannot see why this shouldn't work.

    Do you know how many loops to expect?

     

    Although it should be lazy evaluating - in case it doesn't, you can shorten the command like this:

     

    Source = Web.Contents([SourceURI],[Headers=[#"Authorization​"="WRAP access_token=""client=XXX&user_token=YYY"""]]),
            ImportedXML = Xml.Tables(Source,null,1252)[NextPageURI]{0}

      ],
        each [SourceURI]

     

    This should only retrieve the value from column "NextPageURI" in the first row {0}

     

    Well actually, maybe it's this: Text.PositionOf([SourceURI],"N/A") = -1 : You're searching for something that shouldn't be there. Could you replace it by a positive identification?

    • nakia's avatar
      nakia
      Frequent Visitor
      10 years ago

      Hi,

       

      Just wondering if you ever got this to work?

       

      I have almost the exact same need right now that I would like to resolve.

       

      The API I am talking to has a starting URI and if it has more that 10 records to return it will paginate and provide in the initial response the "NextURI" for me to retrieve the next 10 records.

       

      Appreciate any help I could get!

       

       

      • tempranello's avatar
        tempranello
        Advocate I
        10 years ago

        Hi there

         

        Sorry for the delay in getting back to you.  I did get it to work, but it is slow.  I'm unsure where the bottleneck is (my code, or the service I'm hitting).  Either way, here is the code I pulled together:

         

        let
    /*
      Purpose: Request all timesheet records from Dovico      
    */

    /*
      Generate a list of XML objects based on the Dovico's pagination of its return set
      The initial URL is:  https://api.dovico.com/TimeEntries/?version=5
      Pagination is controlled by return randomly-generated values in URI attributes PrevPageURI and NextPageURI
      The limit in each direction is denoted by the value "N/A"
      Date filtering can be introduced by using:  https://api.dovico.com/TimeEntries/?daterange=2016-04-01%202016-04-15&version=5
      See the Dovico API for more information:  http://apideveloper.dovico.com/Time+Entries
    */
    DataList = List.Generate(
                    ()=> [SourceURI="https://api.dovico.com/TimeEntries/?version=5",ImportedXML=""],
                    each Text.PositionOf([SourceURI],"N/A") = -1,
                    each [
        Source = Web.Contents([SourceURI],[Headers=[#"Authorization"="WRAP access_token=""client=CLIENT_TOKEN&user_token=USER_TOKEN"""]]),
        ImportedXML = Xml.Tables(Source,null,1252),
        ChangeType = Table.TransformColumnTypes(ImportedXML,{{"PrevPageURI", type text}, {"NextPageURI", type text}}),
        SourceURI = Record.Field(Table.First(ChangeType),"NextPageURI"),
        TimeEntries = ChangeType{0}[TimeEntries],
        TimeEntry = TimeEntries{0}[TimeEntry]
        ],
    each [[SourceURI],[ImportedXML]]
    ),

    /*
      Now expand and shape the list of XML objects into a single table of data
    */
    ConvertToTable = Table.FromList(DataList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"ImportedXML"}, {"ImportedXML"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([ImportedXML] <> "")),
    #"Expanded ImportedXML" = Table.ExpandTableColumn(#"Filtered Rows", "ImportedXML", {"TimeEntries"}, {"TimeEntries"}),
    #"Expanded TimeEntries" = Table.ExpandTableColumn(#"Expanded ImportedXML", "TimeEntries", {"TimeEntry"}, {"TimeEntry"}),
    #"Expanded TimeEntry" = Table.ExpandTableColumn(#"Expanded TimeEntries", "TimeEntry", {"Sheet", "Client", "Project", "Task", "Employee", "Date", "TotalHours", "Description"}, {"Sheet", "Client", "Project", "Task", "Employee", "Date", "TotalHours", "Description"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded TimeEntry",{{"Date", type date}, {"TotalHours", type number}, {"Description", type text}}),
    #"Expanded Project" = Table.ExpandTableColumn(#"Changed Type1", "Project", {"Name"}, {"Project.Name"}),
    #"Expanded Task" = Table.ExpandTableColumn(#"Expanded Project", "Task", {"Name"}, {"Task.Name"}),
    #"Expanded Employee" = Table.ExpandTableColumn(#"Expanded Task", "Employee", {"Name"}, {"Employee.Name"}),
    #"Expanded Client" = Table.ExpandTableColumn(#"Expanded Employee", "Client", {"Name"}, {"Client.Name"}),
    #"Expanded Sheet" = Table.ExpandTableColumn(#"Expanded Client", "Sheet", {"Status"}, {"Sheet.Status"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Sheet",{{"Client.Name", "Customer"}, {"Sheet.Status", "Approval status"}, {"Project.Name", "Project"}, {"Task.Name", "Task"}, {"Employee.Name", "Raw Name"}, {"TotalHours", "Effort (hrs)"}}),
    #"Added Week Ending" = Table.AddColumn(#"Renamed Columns", "Week ending", each Date.EndOfWeek([Date],Day.Saturday), type date),
    #"Added Name" = Table.AddColumn(#"Added Week Ending", "Name", each Text.Combine({List.Last(Text.Split([Raw Name],",")), List.First(Text.Split([Raw Name],","))}, " "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Name",{"Raw Name"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Name", Text.Trim}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Trimmed Text",{"Name", "Date", "Week ending", "Customer", "Project", "Task", "Effort (hrs)", "Description", "Approval status"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Description", "Approval status"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Date", Order.Descending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Customer", type text}, {"Project", type text}, {"Task", type text}})
in
    #"Changed Type"