Forum Discussion
how to create a query that paginates?
Hi
I've been watching this thread, in particular the conversation between Imke & kroll, in the hope that I may get the help I need. It seems that solution provided by Imke to kroll might do the trick for me, I want to return all records. I've attempted to reverse engineer the code but it returns an error. I've provided the code with the credentials to a test data source in the hope that someone might be able to assist.
let
Pagination = List.Skip(List.Generate( () => [Last_Key = 1, Counter=0], // Start Value
each [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://api.capsulecrm.com/api/v2/parties?page='"&[Last_Key]&"'",[Headers=[Authorization="Bearer WAWjGWU6Kbl4o9TeGMRw5i52+kvSiz9xfQe+vNTxlcjw61R7RZYa4HxvdT8TSlDG"]])), // retrieve results per call
Last_Key = if [Counter]<=1 then 1 else WebCall[lastKey] ,// determine the LastKey for the next execution
Counter = [Counter]+1,// internal counter
#"Converted to Table" = Record.ToTable(WebCall), // steps of your further query
Value = #"Converted to Table"{1}[Value] // last step of your further queries
],
each [Value]),1),
Pagination1 = Pagination{0}
in
Pagination1
Kind Regards - Grant
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"
