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?

Pls check this code:

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 [ Last_Key = try if [Counter]<=1 then "20170404130408053410572" else [WebCall][lastKey] otherwise null,// determine the LastKey for the next execution
                       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
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column3" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"hits"}, {"hits"}),
    #"Expanded hits2" = Table.ExpandListColumn(#"Expanded Column3", "hits"),
    #"Expanded hits3" = Table.ExpandRecordColumn(#"Expanded hits2", "hits", {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"}, {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"})
in
    #"Expanded hits3"

in your web call - in second part(else part) you are giving a variable inside web.contents - which is not supported in power bi service for scheduled refresh.

Thanks to excellent blog by Chris Webb - this can be solved - or lets say there is a way to overcome this issue.

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

In your case, you would need to prvide a fixed value here instead of "&Last_Key&" 

"https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset="&Last_Key&""))

 with query paramters as folow

Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset=someFixedValue",Query=[offset=Last_Key])

This someFixedValue has to be some valid value which works fine - say your first value of key - which will be used as a dummy value only to 'trick' the PBI service.

BR

emudria.

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!

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

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

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

Is this what you expect?

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?

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

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!!!

Hi Imke,

You query pulls the data (thank you!), but I cannot load that into a table. I have no idea how to deal with the 'error' in the table that is generated.

I run this code:

//Previous code with access credentials 
let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170411202029578674183", 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=last7&limit=10&offset="&[Last_Key]&"&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993")), // retrieve results per call
     			Last_Key = if [Counter]<=1 then "20170411202029578674183" 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),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"}, {"Column1.id", "Column1.accessTime", "Column1.entity", "Column1.browser", "Column1.os", "Column1.location", "Column1.conversions", "Column1.type", "Column1.ip", "Column1.isSpider", "Column1.isUnique", "Column1.trackedParameters"})
in
    #"Expanded Column2"

When try to load the table, I get:

Which seems to be caused by the last records here:

And I cannot simply delete that row:

Do you have any recommendations?

Thank you in advance!

In this thread it's a comment that is repeated a lot:

Imke, you are great!

Thank you for your help!!!

let
    Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY")) else Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
    ),1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"HasError", "Value"}, {"Column1.HasError", "Column1.Value"}),
    #"Expanded Column1.Value" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Value", {"records", "offset"}, {"Column1.Value.records", "Column1.Value.offset"}),
    #"Expanded Column1.Value.records" = Table.ExpandListColumn(#"Expanded Column1.Value", "Column1.Value.records"),
    #"Expanded Column1.Value.records1" = Table.ExpandRecordColumn(#"Expanded Column1.Value.records", "Column1.Value.records", {"id", "fields", "createdTime"}, {"Column1.Value.records.id", "Column1.Value.records.fields", "Column1.Value.records.createdTime"}),
    #"Expanded Column1.Value.records.fields" = Table.ExpandRecordColumn(#"Expanded Column1.Value.records1", "Column1.Value.records.fields", {"Result id", "Question", "Item", "Report id", "Result", "Zero tolerance", "Comment"}, {"Column1.Value.records.fields.Result id", "Column1.Value.records.fields.Question", "Column1.Value.records.fields.Item", "Column1.Value.records.fields.Report id", "Column1.Value.records.fields.Result", "Column1.Value.records.fields.Zero tolerance", "Column1.Value.records.fields.Comment"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Column1.Value.records.fields", {"Column1.Value.records.fields.Question", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column1.Value.records.fields.Report id", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Values1",{{"Column1.Value.records.fields.Result", Int64.Type}, {"Column1.Value.records.fields.Zero tolerance", type text}})
in
    #"Changed Type"

This snippet of code works perfectly for fetching data from Airtable and working with it in the Power BI Desktop app. As well as publishing to online. However, when I try to automate data refresh through the online interface Power BI lets me know it's not supported even though it clearly is a Web data source:

"You can't schedule refresh for this dataset because the following data sources currently don't support refresh:"

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY")),
    records = Source[records],
    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Result id", "Question", "Item", "Report id", "Result", "Zero tolerance", "Comment"}, {"Column1.fields.Result id", "Column1.fields.Question", "Column1.fields.Item", "Column1.fields.Report id", "Column1.fields.Result", "Column1.fields.Zero tolerance", "Column1.fields.Comment"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Column1.fields", {"Column1.fields.Question", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column1.fields.Report id", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Values1",{{"Column1.fields.Result", Int64.Type}})
in
    #"Changed Type"

If I redo the web connection and specify my column structure again I get the same data but only limited to 100 rows. This time online refresh is supported though. 

Any ideas on how we could work around this? Or how to specify the code below to be online refresh compatible? 

I got it to work by adding extra [] brackets. 

Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset=0",[Query=[offset=Last_Key]])),

Thanks!

--- OLD POST ---

Thanks for getting back to me!

I've tried your formula but am still getting errors. 

WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY")) else Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset=0",Query=[offset=Last_Key])), // retrieve results per call

Basically the first 100 rows (when counter <1) always load. But then regardless of what fixed value I set, even with no fixed value, the 101 row returns an error.

Likewise, if I input the URL in the browser offset=0 returns values (probably the same 100 first that's also loaded into Power BI) and anything else I've tried >0 returns error. If I set offset=1, I get "{"error":{"type":"INAVLID_OFFSET_VALUE","message":"The value of offset 1 is invalid"}}