Offset value invalid; output does not match API documentation

[Bastiaan_Bijl]

Hello,

I am trying to build a query in Power Query/Power BI to load an Airtable table into the data model. I have found some working code which uses the offset parameter returned by the API to deal with the pagination.

However, the offset value returned does not match the API documentation and gives an error when I try to parse it.

According to the API docs, the offset should look like this:

"offset": "reco1XxXxXxXxXxXxXx"

However, what I get is this:

"offset":"itrZXxXxXxXxXxXxXxXx/rec5XxXXXxXxXxXxXx"

When I parse the value into the URL, using &offset=itrZXxXxXxXxXxXxXxXx/rec5XxXXXxXxXxXxXx, I get the following error:

{"error":{"type":"LIST_RECORDS_ITERATOR_NOT_AVAILABLE"}}

When I try passing either of the values (before and after the /), then the error is:

{"error":{"type":"INVALID_OFFSET_VALUE","message":"The value of offset itrZXxXxXxXxXxXxXxXx is invalid"}}

Am I missing something here?

Thanks!

[Bill_French]

Bastiaan_Bijl:

“offset”:“itrZXxXxXxXxXxXxXxXx/rec5XxXXXxXxXxXxXx”

Hmmm, I have never encountered this in a paginated offset. I have no idea what an “itr” object ID even is.

Have you tried parsing just the “/rec…” portion and using that in the offset URL?

[Bastiaan_Bijl]

Bill.French:

Hmmm, I have never encountered this in a paginated offset. I have no idea what an “itr” object ID even is.

Have you tried parsing just the “/rec…” portion and using that in the offset URL?

Hi Bill,

Yes, I have tried that (without the slash) and got the following error:

{"error":{"type":"INVALID_OFFSET_VALUE","message":"The value of offset rec5XxXxXxXxXxXxXxXx is invalid"}}

Strange, right?

Bastiaan

[Bill_French]

Yes, strange indeed.

There’s a reason your offsets are getting a complex itr…/rec… ID - we need someone to explain why and perhaps we need to see the query that is able to produce this off result.

[JonathanBowen]

Is it this, perhaps?

Iteration may timeout due to client inactivity or server restarts. In that case, the client will receive a 422 response with error message LIST_RECORDS_ITERATOR_NOT_AVAILABLE . It may then restart iteration from the beginning.

Still, the “itr” record needs some more explanation.

[Bill_French]

JonathanBowen:

Iteration …

Hmmm, itr... - iteration - related? Perhaps.

[JonathanBowen]

Yep, that was my thought

[Bastiaan_Bijl]

Tested with another table in the same base, and I get the same weird combination of itr/rec back.

I guess that you guys do not have this issue when querying the API now?

I have sent a ticket to Airtable support about this issue as well. If they respond, I’ll let you guys know.

[Bill_French]

Bastiaan_Bijl:

you guys do not have this issue when querying the API now?

Correct - I (for one) have never seen this before. I have no knowledge of an “itr” object either, so I’m curious.

But, do we connect to and query Airtable the same way you do? Can’t say since we cannot see your code. Feel free to share more insights.

[Bastiaan_Bijl]

Bill.French:

But, do we connect to and query Airtable the same way you do? Can’t say since we cannot see your code. Feel free to share more insights.

I have two ways of querying: via Power Query in Excel and directly in my browser. I use the base URL of the API, with the API key parsed as a parameter in the URL. Both methods essentially use the same curl method, and give the same offset value back.

This is the Power Query/M code I use:

let
     Pagination = List.Skip( List.Generate( () => [Page_Key = "init", Counter=0],                                                                                   // Start Value
   	                     each  [Page_Key] <> null,                                                                                                                  // Condition under which the next execution will happen
                         each  [Page_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/appdID/Table_Name?api_key=keyXXXXXXXXXXXXX"))
			                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/appdID/Table_Name?api_key=keyXXXXXXXXXXXXX&offset="&[WebCall][Value][offset])),// retrieve results per call
			                     Counter = [Counter]+1                                                                                                              // internal counter
                                ],
   		                  each [WebCall]
                        ),
                      1
                   ),
     #"Json2Table" =  Table.RenameColumns(Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),{{"Column1", "stepA.1"}}),
     #"Expanded to stepA.2" = Table.ExpandRecordColumn(#"Json2Table", "stepA.1", {"Value"}, {"stepA.2"}),
     #"Expanded to stepA.3" = Table.ExpandRecordColumn(#"Expanded to stepA.2", "stepA.2", {"records"}, {"stepA.3"}),
     #"Rows from stepA.3" = Table.RenameColumns(Table.ExpandListColumn(#"Expanded to stepA.3", "stepA.3"),{{"stepA.3", "stepB.1"}}),
     #"Source" = Table.ExpandRecordColumn(#"Rows from stepA.3", "stepB.1", {"fields"}, {"Src"}),
     #"Expanded Src" = Table.ExpandRecordColumn(Source, "Src", {"COLUMN NAMES"})
in
     #"Expanded Src"