cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
spocx Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

That is perfectly allright ImkeF:-) I am just grateful, that you want to help me out.

 

With the last code you posted, I get the following error:

Capture3.PNG

Super User
Super User

Re: how to create a query that paginates?

Hm, let's see if there will be any code left :-)

 

let
Pagination = List.Skip(List.Generate( () => [Page = 1, Counter=0], // Start Value
   		each  [Counter]<5, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://api.itrp.qa/requests?per_page=100&page="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Aim is to get a list of responses and then to see which further transformations are needed and how to define the looping-condition.

 

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




spocx Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

Now we got a hit:-) I am able to turn it in to table and expand the columns. I am just not seeing the number of columns and values as i would expect from the data source:

Capture4.PNGCapture5.PNGCapture6.PNG

Super User
Super User

Re: how to create a query that paginates?

So yes, here we see the problem: You were expecting the WebCall to return a record with a field "requests" in it, but here it returns a list instead. Therefore your original code: Table = Table.FromRecords(WebCall[requests]) couldn't work.

 

Now expand the column "WebCall" to see what is actually returned instead.

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




spocx Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

That is amazing ImkeF. Now I am able to see the columns and values as expected. And the code seems to work, as I can get more than 100 rows.

 

But I need to know the amount of counts to get all the rows. It would be so smart if that was not nessecary, because the data source will change often.

Super User
Super User

Re: how to create a query that paginates?

Try this:

 

let
Pagination = List.Skip(List.Generate( () => [WebCall={}, Page = 1, Counter=0], // Start Value
   		each List.Count([WebCall])>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://api.itrp.qa/requests?per_page=100&page="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

 

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




spocx Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

This is amazing ImkeF and is working perfectly:-) Thank you so much.

 

I have another system I need to do the same with, but the last "automatic" code does not work.

 

It works perfectly fine with the code where I must know the amount of rows:

let
Pagination = List.Skip(List.Generate( () => [Page = 1, Counter=0], // Start Value
   		each  [Counter]<30, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+10,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

But not with the code where it automatically stops when there is no more rows:

let
Pagination = List.Skip(List.Generate( () => [WebCall={}, Page = 1, Counter=0], // Start Value
   		each List.Count([WebCall])>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

I get the following error:

2017-11-22_09-24-29.png

Super User
Super User

Re: how to create a query that paginates?

Pleased to hear :-)

 

In your new case, the WebCall doesn't return its results in the format of a list, but in a record instead. So we can modify the looping-conditions that it counts the number of fields from the last record: If there are any, then continue else stop like this:

 

 

let
Pagination = List.Skip(List.Generate( () => [WebCall={}, Page = 1, Counter=0], // Start Value
   		each List.Count(Record.FieldNames([WebCall]))>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

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




spocx Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

That sounds very good, but I still recieve almost identical error with the new code:

2017-11-22_10-15-23.png

Super User
Super User

Re: how to create a query that paginates?

That's probably because the starting value has to be adjusted to record-format as well Smiley Happy

 

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
   		each List.Count(Record.FieldNames([WebCall]))>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

 

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