cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Grant Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Hi Imke

 

Absolutely brilliant. That is exactly what I needed. Thank you for your assistance and rapid turnaround.

 

Kind Regards - Grant

cartman21 Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

Hi,

 

I've been trying to solve my query using this thread, but not really sure how to go about it.

 

Basically, the database I'm connected to cuts off values at 500 per query, so im doing 0-500, then 500-1000, then 1000-1500 and so on.

 

Here is the code (with changed url/api key for security reasons)

 

let
Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start=0&limit=1499&api_token=xxxxxx")),
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", "owner_id", "name", "open_deals_count", "re "Column1.cc_email"})
in
#"Expanded Column1"

 

 

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

As you see, in the data above, I try to query the data from start=0 to limit=1499, but i still only get values 0 to 500. I have about 1400 values in the actual data. 

 

Any help would be much appreciated.

 

cartman21 Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

If anyone can let me know, it would be amazing, thanks a lot!

Super User
Super User

Re: how to create a query that paginates?

Hi @cartman21,

if you know beforehand that you need to split up your calls into 3 chunks and don't have to retrieve values from the current call to make the next one, you can use a much simpler logic than the List.Generate-versions in this thread.

You start with creating a table that holds all the different URL-parameters that you then pass on to a function. Expand that resulting table & you're done:

 

let
// table with your query intervalls
Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}}),
// Call Function
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="&Limit&"&api_token=xxxxxx")),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", "owner_id", "name", "open_deals_count", "re "Column1.cc_email"})
in
#"Expanded Column1"
in
CallFunction

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




cartman21 Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

Hello @ImkeF ! Thanks for taking the time to help me:

 

Here is what I have based on your help:

 

The relevant urls are in a table named Table2

 

 

 

let
//Table2
Source = #table({"Start", "Finish"}, {{0, 500},{501, 1000},{1001, 1500}, {1501, 2000}}),
//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="&Limit&"&api_token=xxxxxxxxxxxxxxxx")),
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"

 

 

I unfortunately get a "Token Comma Expected" Error. Are you quickly able to tell me where I have gone wrong?

Super User
Super User

Re: how to create a query that paginates?

My fault, sorry - didn't test the query. Pls try this:

 

let
//Table2
 Source = #table({"Start", "Finish"}, {{0, 500},{501, 1000},{1001, 1500}, {1501, 2000}}),
//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=xxxxxxxxxxxxxxxx")),
 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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




cartman21 Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

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

 

 

 

 

Super User
Super User

Re: how to create a query that paginates?

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: how to create a query that paginates?

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: how to create a query that paginates?

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries