cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kroll Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Imke, thank you for the example. I agree, it works.

I'll try to play around with both versions of the code to make it work!

I hope to be back with my findings in a few days.

Thank you for your help!

mhaywardm Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Hi ImkeF,

 

I have a similar issue to others in the thread.  I just cant seem to get my query to paginate.  I've done some initial exploring/research which will hopefully limit the amount of effort needed to solve this.  I'm very new to power BI and have little coding experience so any help would be appriciated.  Below is the code I've been playing around with (I've removed my token):

 

let
 iterations = 20,
 url = "https://az1.qualtrics.com/API/v3/mailinglists/ML_cwQxQJJ5adc1YyN/contacts", 
 FnGetOnePage =
   let
    Source = Json.Document(Web.Contents("https://az1.qualtrics.com/API/v3/mailinglists/ML_cwQxQJJ5adc1YyN/contacts", [Headers=[#"X-API-TOKEN"="my token here"]])),
    data = try Source[result][elements] otherwise null,
    next = try Source[result][nextPage] otherwise null,
    res = [Data=data, Next=next]
   in
    res,
 
 GeneratedList =
  List.Generate(
   ()=>[i=0, res = FnGetOnePage(url)],
   each [i]<iterations and [res][Data]<>null,
   each [i=[i]+1, res = FnGetOnePage([res][Next])],
   each [res][Data])
in
    GeneratedList

As you can see, I'm using an API with headers which may be the cause of some of the issues I'm having.  The url for the next page of data is in a field called "nextPage".  The recors are in a field called "elemts".  There are only 100 records per page and I have a few thousand records that I'd like to automatically bring in.  

 

Using the code above, I get the FnGetOnePage to run fine and it's producing the correct data in the "Data" and "Next" fields.  However, the List.Genreate function is where I'm getting an error.  Below is the error I'm getting:

 

Expression.Error: We cannot convert a value of type Record to type Function.
Details:
Value=Record
Type=Type

 

You seem to know what you're doing when it comes to this topic, I would love to hear your feedback or any suggestions you might have!

 

Thanks Smiley Happy

Super User
Super User

Re: how to create a query that paginates?

Yes, you're code is looking very good - especially for a "beginner" - kudos!

I haven't changed much, pls see if the following code works for you:

 

let
 iterations = 20,
 url = "https://az1.qualtrics.com/API/v3/mailinglists/ML_cwQxQJJ5adc1YyN/contacts", 

// Turn your query into a function where the url is fed in as a parameter
 FnGetOnePage = (url) =>
   let
   // Replace the hardcoded url to a reference to the parameter that's going to be fed in
    Source = Json.Document(Web.Contents(url, [Headers=[#"X-API-TOKEN"="my token here"]])),
    data = try Source[result][elements] otherwise null,
    next = try Source[result][nextPage] otherwise null,
    res = [Data=data, Next=next]
   in
    res,
 
 GeneratedList =
  List.Generate(
   ()=>[i=0, res = FnGetOnePage(url)],
   each [i]<iterations and [res][Data]<>null,
   each [i=[i]+1, res = FnGetOnePage([res][Next])],
   each [res][Data])
in
    GeneratedList

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




kroll Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Imke,
Once again, thank you for the previous guidance, but I need to admit a defeat... was not able to make it work.

 

I understand the example code you provided, but I cannot bridge the difference between the example and my situation.

Could you please take a look at the code below for any obvious errors?

 

Also, I've added access credentials to a sample data, in case you want to try to run it.

Thank you for your assistance.


(For others who may want to try the code: FYI, the authKey will be revoked in a few days)

 

//Previous code with access credentials 
let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170404130408053410572", 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://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=last30&limit=100&offset="&[Last_Key]&"%408693934&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993")), // retrieve results per call
     			Last_Key = if [Counter]<=1 then "20170404130408053410572" 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) // Select just the Record of the last step from your query
in
Pagination


//Is there an easy way to incorporate the line below into the code above?
//This would dynamically pull the first value for Last_Key
Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=last30&limit=100&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993"))[lastKey]

 

I've also tired, based on the example you provided, a striped out of other elements version the code.

I could make it work, not to mention moving forward with pulling dynamic lastKey, etc.

let
Pagination =
List.Generate(() => [Result = Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=yesterday&offset="&[Last_Key]&"%4041627&limit=100&authKey=4EEB59D4-14F7-4DA0-9431-CE151011FCF0"))[hits], Last_Key = "20170404130408053410572"],
			each 	[Last_Key] <> null and [Last_Key] <> "",
			each	[Result = Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=yesterday&offset="&[Last_Key]&"%4041627&limit=100&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993"))[hits],
			        Last_Key = [lastKey]], 
			each	[Result]
)
in
    Pagination

//Another Version:
let
Pagination =
List.Generate(() => [Result = Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=yesterday&offset="&[Last_Key]&"%4041627&limit=100&authKey=4EEB59D4-14F7-4DA0-9431-CE151011FCF0"))[hits], Last_Key = "20170404130408053410572"],
			each 	[Last_Key] <> null and [Last_Key] <> "",
			each	[Result = Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=yesterday&offset="&Text.From(Last_Key)&"%4041627&limit=100&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993"))[hits],
			     Last_Key = [lastKey]],
                        each	[Result]
)
in
    Pagination

 

 

Super User
Super User

Re: how to create a query that paginates?

Ooops, I'm really sorry: The closing square bracket was at the wrong place. I've moved the steps around and it shifted to the wrong place:

 

//Previous code with access credentials 
let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170404130408053410572", 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://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=last30&limit=100&offset="&[Last_Key]&"%408693934&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993")), // retrieve results per call
     			Last_Key = if [Counter]<=1 then "20170404130408053410572" 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) // Select just the Record of the last step from your query in Pagination

Works for me now, just expand the record (& ignore the error-message for a start): Transfer the list to a table & then you can expand the records you need.

 

 

Not sure about your other questions/aspects from your post: Is there anything that is still to be done now?

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




kroll Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

There is still a problem with that code... It returns the first pass (1st 100 records).

I've changed the 'limit' to 10, to make the issue more visible.

 

Thoughts?

//Previous code with access credentials 
let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170404130408053410572", 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://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=last30&limit=10&offset="&[Last_Key]&"%408693934&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993")), // retrieve results per call
     			Last_Key = if [Counter]<=1 then "20170404130408053410572" 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) // Select just the Record of the last step from your query
in
Pagination
Super User
Super User

Re: how to create a query that paginates?

Yes, the limit-parameter will do that. If you set it to 1000, 701 rows will be returned.

Is this what you expect?

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




kroll Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Sorry, I was not clear in my comment. I'm trying to point out that the code does not paginate as expected.

 

If a dataset has more record than the limit, the next page should have the records that follow the previous page, until all records are loaded. lastKey from the first load has the start value for the next "page".

As you pointed out, there are over 700 records, but the the code returns only the first page.

 

If you run the query without 'limit' parameter, 50 records (that's the default) will be return. The code should use lastKey to pull the next page, but it doesn't.

Do you have any ideas what could be the problem with the code?

Super User
Super User

Re: how to create a query that paginates?

How many records to you expect? (unique id's)

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




kroll Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Imke, I had a feeling that there was a disconnect between what we were seeing...

The bottom line - you are GREAT, and your code is correct, and I appreciate your help VERY MUCH!

Thank you!!!

 

More details, in case others repeat my mistake...

Regardless of the setting on 'limit=', the returned number of records is the same. Basically, your query works exactly as expected!

My confusion was caused by the fact that expanding content of the 'Error' in the initial table would show the records pulled by the first pass (w/o pagination).

 

I should have paid more attention to your instructions "just expand the record (& ignore the error-message for a start): Transfer the list to a table & then you can expand the records you need.".

 

It works great.

Once again, THANK YOU!!!