Forum Discussion
how to create a query that paginates?
Hi Grant,
please try this:
let
Pagination = List.Skip(List.Generate( () => [Table = #table({}, {{}}) ,Page = 1, Counter=0], // Start Value
each Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://api.capsulecrm.com/api/v2/parties?perPage=100&page="&Text.From([Page])&"",[Headers=[Authorization="Bearer WAWjGWU6Kbl4o9TeGMRw5i52+kvSiz9xfQe+vNTxlcjw61R7RZYa4HxvdT8TSlDG"]])), // retrieve results per call
Page = [Page]+1,
Counter = [Counter]+1,// internal counter
Table = Table.FromRecords(WebCall[parties]) // steps of your further query
]
,each [Table]
) ,1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"id", "firstName", "lastName", "createdAt"}, {"id", "firstName", "lastName", "createdAt"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Column1", {"id"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [id] <> null)
in
#"Filtered Rows"
Hi Imke
Absolutely brilliant. That is exactly what I needed. Thank you for your assistance and rapid turnaround.
Kind Regards - Grant
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.
If anyone can let me know, it would be amazing, thanks a lot!
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
