cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: how to create a query that paginates?

Hi Mike,

if the URL for the next page looks like so:

http://example.co.uk/example/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&pa... ,

everything you have to do is to create a table with one row per necessary call:

 

Table.FromColumns({{1..300000/100}})

 

 

Add another column where you reference the first column as a parameter/variable to your web call:

 

Web.Page(Web.Contents("http://example.co.uk/example/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&page="&Text.From([Column1])))

 It doesn't matter in what format the result will be returned. Build a function that retrieves one of it and apply it as a next step to every (returned results in each) 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




mbrough Member
Member

Re: how to create a query that paginates?

Hi ImkeF,

 

I'm getting an error message around 

&Text.From([Column1]))

 

Expression.Error:There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression? 

 

Regards

 

Mike

Super User
Super User

Re: how to create a query that paginates?

Are you using it within a "Table.AddColumn"-Command?

If yes, please share full query code

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




mbrough Member
Member

Re: how to create a query that paginates?

Hi,

 

Please see below. Sorry if my code is way off but I am new to this type of language and PBI.

 

let
    Source = Table.FromColumns({{1..500/100}}),
    GetData = Table.AddColumn(Xml.Tables(Web.Contents("http://example.co.uk/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&page="&Text.From([Column1]))))

in
    GetData

Thanks for your help

 

Mike

Super User
Super User

Re: how to create a query that paginates?

let
    Source = Table.FromColumns({{1..500/100}}),
    GetData = Table.AddColumn(Source, "New", each Xml.Tables(Web.Contents("http://example.co.uk/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&page="&Text.From([Column1]))))

in
    GetData

You've handcoded it - using the UI would have given you the additional values ;-)

 

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




mbrough Member
Member

Re: how to create a query that paginates?

Thank you! That's worked perfectly! Smiley Happy

 

Regards

 

Mike

Grant Frequent Visitor
Frequent Visitor

Re: 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

Super User
Super User

Re: 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
    Custom1

It returns results, but I couldn't spot any NextKey, so you might have to limit the number of pages manually.

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




Grant Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

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

Super User
Super User

Re: 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"

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