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
cartman21's avatar
cartman21
Icon for Helper I rankHelper I
9 years ago

Hello again ImkeF

 

Something seems to work, but it breaks for values 1500 and beyond, where I get an error.

 

The error is Expression.Error: We cannot convert the value null to type list.

 

Details

Value = 

Type=Type

 

Which I don't understand

 

Further, while the code holds up for values 0-1500, most of the values are null, but when the query is run individually, I get values in the tables.

 

When I run to advanced query, and expand the tables, this is what shows up:

let
//Table2
 Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}, {1500, 1999}}),
//CallFunction
 CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish]))),
//Function
Function = (Start as text, Finish as text) =>
let
 Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Finish&"&api_token=xxxxxxxx)),
 data = Source[data],
 #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"})
in
 #"Expanded Column1"
in 
CallFunction

 

 

 

 

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

Unfortunately that's a mess now. Please start with this query again. I've reordered the steps to make further expansions easier for you:

 

let

Function = (Start as text, Finish as text) =>

let
 Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Finish&"&api_token=xxxxxxxx")),
 data = Source[data],
 #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"})
in
 #"Expanded Column1",

//Table2
 Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}, {1500, 1999}}),
//CallFunction
 CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish]))),
//Function
    #"Expanded CallFunction" = Table.ExpandTableColumn(CallFunction, "CallFunction", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"}, {"CallFunction.id", "CallFunction.company_id", "CallFunction.user_id", "CallFunction.done", "CallFunction.type", "CallFunction.reference_type", "CallFunction.reference_id"})
in
    #"Expanded CallFunction"
  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    9 years ago

    With regards to the errors above 1500 you can inject an error-handler like this:

     

    CallFunction = Table.AddColumn(Source, "CallFunction", each try Function(Text.From([Start]),Text.From([Finish])) otherwise #table({"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"}, {})),

    This returns a blank table with your column names in case the webcall fails.

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

    null returns could be due to wrong field names in the expansion of the record. You could try this:

     

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", Record.FieldNames(#"Converted to Table"[Column1]{0}) )

    this will expand all fields that have the same field names as the record of the first row