Forum Discussion
how to create a query that paginates?
Hi Grant,
please check out this code:
let
Pagination = List.Skip(List.Generate( () => [Last_Key = 0, Counter=0], // Start Value
each [Counter]<4, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://api.capsulecrm.com/api/v2/parties?page="&Text.From([Last_Key])&"",[Headers=[Authorization="Bearer WAWjGWU6Kbl4o9TeGMRw5i52+kvSiz9xfQe+vNTxlcjw61R7RZYa4HxvdT8TSlDG"]])), // retrieve results per call
Last_Key = [Last_Key]+1,
Counter = [Counter]+1,// internal counter
Table = Table.FromRecords(WebCall[parties]) // steps of your further query
//Value = #"Converted to Table"{0}[Value] // last step of your further queries
]
,each [Table]
) ,1),
Custom1 = Table.Combine(Pagination)
in
Custom1It returns results, but I couldn't spot any NextKey, so you might have to limit the number of pages manually.
Hi Imke
Thank you for your rapid response. As you will probably gather from the questions to follow, I'm a Power Query/BI novice. the code you provided has pointed me in the right direction, and with some minor modifications, I get what I want - see code below.
let
Pagination = List.Skip(List.Generate( () => [Page = 1, Counter=0], // Start Value
each [Counter]<50, // 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
//Value = #"Converted to Table"{0}[Value] // last step of your further queries
]
,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"What I'm actually trying to accomplish is to code so that I dont have to limit the number of pages manually. I want to stop the execution when the number of results returned are zero.
You will also note that I have had to modify the code remove duplicates and null values in order to arrive at the expected result i.e. 241 records.
Hope you can assist.
Kind Regards - Grant
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.
Thanks ImkeF
I am almost managing to implement the below query in my work. I have previously managed to use something similar by defining a set number of pages - however it requires a lot of maintenance as the number of total pages per query increases periodically. I have used your below query, however I am encountering two errors situationally:
1. Some of the tables contain Records as separate columns, but when there is a row with a 'null'/blank instead of [Record] (i.e. there is no record for that field) the whole row turns into an error. After playing around a bit, I believe the problem is in how 'Pagination' creates the tables(as when I click on the step they already are an error), not sure how to fix it though? The final query I am using:
let Pagination = List.Skip(List.Generate( () => [Table = #table({}, {{}}) ,Pages = 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("link"&page=1", [Query=[page=Text.From([Pages])]])), // retrieve results per call Pages = [Pages]+1, Counter = [Counter]+1,// internal counter Table = Table.FromRecords(WebCall[customers]) // steps of your further query ] ,each [Table] ) ,1), #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"2. I have an API which has an returns an 'Error 404 page not found' when it reaches a page that doesn't exist but rather than loading all the data so far, it just errors out. I previously had problems as well with my manual version of pagination and had to use two less pages than maximum (i.e. if the error was at page 157 I used 155 which also pulled page 156). Any thoughts on how to handle this?
Many thanks,
Daniel
Just as a quick update, I managed to Bypass #1 by not pulling the column creating an error from
Table = Table.FromRecords(WebCall[customers],{"Col1","Col2",.."Colx"})However ideally I'd still like to use the records where they exist. Also if the error will pop up in another column I might not spot it. Ideally there would be some kind of error handling for null values?
Thanks,
Daniel
