Sure! Please find it below:

let


     URL            = "https://api.airtable.com/v0"         , //API URL excluding slash


     AppID          = "YOUR_BASE_ID"                        , //Base ID Airtable


     TableName      = "YOUR_TABLE_NAME"                     , //URL-encoded table name


     ApiKeyName     = "api_key"                             , //The name of the API key query parameter





     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( URL & "/" & AppID & "/" & TableName, [ApiKeyName = ApiKeyName] ))


			                                   else  Json.Document(Web.Contents( URL & "/" & AppID & "/" & TableName & "?offset=" & [WebCall][Value][offset], [ApiKeyName = ApiKeyName] )),    // 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"})


in


     Source

let


     URL            = "https://api.airtable.com/v0"         , //API URL excluding slash


     AppID          = "YOUR_BASE_ID"                        , //Base ID Airtable


     TableName      = "YOUR_TABLE_NAME"                     , //URL-encoded table name


     ApiKeyName     = "api_key"                             , //The name of the API key query parameter





     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( URL & "/" & AppID & "/" & TableName, [ApiKeyName = ApiKeyName] ))


			                                   else  Json.Document(Web.Contents( URL & "/" & AppID & "/" & TableName & "?offset=" & [WebCall][Value][offset], [ApiKeyName = ApiKeyName] )),    // 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"})


in


     Source

@Bastiaan_Bijl,

Thanks for sharing the latest version of your code.

Given this new information, it appears the problem was exactly where I initially pinpointed when you first published your code five days ago -

The two versions are shown here - latest on the left and the failing version on the right.

The change was to move the ampersand from the embedded offset URL and where it belongs to concatenate the offset with the URL endpoint.

Ergo… nothing magically started working and something in your code changed to make it appear like there was some magic happening. :winking_face:

Hi Bill,

Thanks for your reply, but I’m afraid I have to respectfully disagree. Please allow me to explain why.

First of all, the ampersand you are referring to in your response to my original code is not embedded. The ampersand is used to concatenate the query string, embedded in quotes and the offset parameter that is defined earlier:

In my latest version of the code, the only thing I have done is parameterize the URL creation so that the user can insert the specifics at the top of the code rather. Furthermore, I have added 2 spaces left and right of the ampersand to improve the legibility of the code:

As you can see, essentially there are no differences between how the query URL is constructed.In fact, if I run the first code now, it works.

Secondly, as I mentioned before, the issues with the errors I got on the iteration did not only occur in Excel/PowerQuery. I also tried pasting the query URL directly into my browser and in Postman, and got the same error.

This leads me to conclude that something went wrong on the API side. They most likely implemented a change in the API to include the itr value to the offset, but did not implement it correctly until a day later.

Cheers,

Bastiaan

Yeah, it’s possible the ampersand played no part in the five days of wondering why your earlier version was unable to run, but, there are some significant changes shown and likely more than a few tests along the way to the latest version.

And that’s my point - you didn’t share the complete picture until now. Without a complete picture of the logic, it’s almost impossible to assess what’s truly happening.

Breaking changes occur in APIs all the time. But when they occur we tend to see a number of people all reporting similar issues. The odds of an API error induced by a developer are astronomically high compared with a breaking change introduced by the vendor and factorially astronomical when no other users report any API issues.

As such, I conclude the exact opposite; it’s extremely likely something in your code probably changed and it started working.

Show of hands - how many API developers have experienced unexplained “sudden” success with an unfamiliar API? :winking_face:

No worries though - it’s great you have a working model now.

Well, if you scroll back up in the post, the entire code I used 5 days ago was there. When testing this morning, I actually copied it from the post because I did not save it anywhere else.

I guess we will never know, so let’s agree to disagree on this point. :winking_face: After all, it is indeed nice to have a working model now.

One last thing: I still get the itrXXXXXXXXXXXX back as part of my offset value. Do you get that as well now?

I definitely want to agree that we disagree. :winking_face:

We version every change when testing new API code. This allows us to know where we may have created issues and more important where APIs are actually failing.

Sure, it’s a little more effort, but the analytics we get from this process accelerate development in the long run and put us in a position where we never feel like we will never know why things failed. This also provides us with clear ammunition to assert claims of another API failing to perform, an important development aspect that allows us to bill for time spent on failures beyond our control.

It’s difficult to say; when stuff works reliably we don’t go looking at offset values that have likely been occurring properly for years. I do know that in many API unit tests recently I have seen only record IDs, not iteration ids. But we must also realize unit tests are designed to produce responses in very narrow ranges of data.

But without question, of the 80+ Airtable API integration processes currently running under our apps every minute, we have seen zero failures from Airtable over the recent past (9+ months) with one exception - an Airtable outage that occurred about two weeks ago. The API impact was very brief (like 2 minutes) while the UI was impacted for a few hours I think.

Hey Guys,

Just wanted to say thank you all for this post!

I am getting the same response ‘itrXXXXXX/recXXXXXX’ for the offset and was unsure how to incorporate with the curl. This had been bugging me for a number of days now!

I used your query to better understand how to properly query for the next page with python’s api requests library: ‘offset’: ‘itrXXXXXX/recXXXXXX’

Glad to be amongst some fellow geeks :grinning_face_with_big_eyes:

In case anyone is still getting the issue, here is what happened with me.


I was getting same error and was making changes here and there but nothing worked. Then I realized, because of iterative offset value, my url was getting elongated every time as it was not replacing old offset but appending in it.


If you are still getting this error, just print your URL just before hitting API and see if it is OK.

In case this helps anyone coming across this issues now...

I was having this issue when running the query in postman, and it ultimately came down to quotation marks.

I thought I had to pass the offset surrounded by quotes, e.g.,

I kept getting the error.

When I removed the quotes and passed,

I got a proper response and no error.