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
5 years ago

Hi Anonymous ,

 

Thanks a lot for your answer!!!

 

Unfortunately I still have a few because, because this topic overtaxes me at the moment a bit.

I added the parameter URL and it works.

 

The next step is to paste all the functions to my query, right?

 

I did it and got a failure on Function: FetchPages, there is a problem with “let”.

I don´t know why.

 

To understand a bit what your code does I just added 1.Function 01:FetchPage to my query.

I got three parameters: URL, SkipRows and pageSize.

 

Do I understood the first function right, that this one can give my table on one page, depending on the pageSize? If I add 2000 to this parameter, I get 2000 rows on one page. However, it´s just static and not dynamical?

 

Sorry for stupid questions, but M-language is difficult for me and try to understand it.

 

thanks a lot for your support and hopefully your patience!

freiburgc

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

I have tried to work with Multi-step function.

 

Unfortunately I got a problem - Expression.Error: The Name "GenerateByPage" wasn't recognized.

 

I tried "Table.GenerateByPage", doesn´t work.

 

let

FetchPages = (url as text, pageSize as number) =>

let

Source =GenerateByPage(
    
    (previous) =>

let

skipRows = if previous = null then 0 else Value.Metadata(previous)[skipRows],

totalItems = if previous = null then 0 else Value.Metadata(previous)[total],

table = if previous = null or Table.RowCount(previous) = pageSize then

FetchPage(url, pageSize, skipRows)

else null

in table,

type table [Column1])

in

Source

in

FetchPages

Maybe you have a look at it or someelse, that would great!!

 

Thanks again!

freiburgc

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Instead of having them all listed in one query, could you please have them created as indicated by me i.e., to create 3 queries and give it a try?

     

    Regards,

    A!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

       

      I have created different queries and it almost works now.

       

      Unfortunately another problem has emerged.

      I have extracted a customfield of the query and i got a failure.

      "Expression Error: Value "null" cannot be converted to Typ List.

      Details:

      Value=

      Type=[Type]

      = Table.TransformColumns(#"Erweiterte Column1.fields.customfield_13101", {"Column1.fields.customfield_13100", each Text.Combine(List.Transform(_, Text.From)), type text})

      Do have an idea how to solve it?

       

      Thanks alot!

      freiburgc