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
LarsSchreiber's avatar
LarsSchreiber
Responsive Resident
10 years ago

Hi gotmike,

 

there is no concept of loops in M (Power Query), but you can use recursive functions, to reach your goals. The following article by Chris Webb uses this concept to flat a parent/child hierarchy. I am not sure how to use this in combination with your API call, but maybe this is one more step into the right direction for you :)

 

Regards,

Lars

tristanstcyr's avatar
tristanstcyr
Helper I
10 years ago

You may also want to avoid recursion as well since we don't do tail recursion optimization. It depends how many results you need to iterate over. If the result is large, look into using functions such as List.Accumulate or List.Generate.

    • 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?