Power query does not upload all records

[LucieCL]

Hi all, 

I have a problem with uploading records via API from Airtable. It only upload 199 records. I use the code shared from Airtable, see below. I use this code over the year but now I have uploaded only 199 records in all my tables. Is it possible to be caused by the API change?

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",[RelativePath="v0/"&BASE_ID&"/"&TABLE_ID,Headers=[Authorization="Bearer "&PERSONAL_ACCESS_TOKEN]]))
else
Json.Document(Web.Contents("https://api.airtable.com",[RelativePath="v0/"&BASE_ID&"/"&TABLE_ID&"?offset="&[WebCall][Value][offset], Headers=[Authorization="Bearer "&PERSONAL_ACCESS_TOKEN]])),// 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", {"Value"}, {"Column1.Value"}),
#"Expanded Column1.Value" = Table.ExpandRecordColumn(
#"Expanded Column1", "Column1.Value", {"records"}, {"Column1.Value.records"}),
#"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"}),
#"Renamed Columns" = Table.RenameColumns(
#"Expanded Column1.Value.records1",{{"Column1.Value.records.id", "_airtableRecordId"},
{"Column1.Value.records.createdTime", "_airtableRecordCreatedAt"},
{"Column1.Value.records.fields", "_airtableRecordFields"}}),
#"Reordered Columns" = Table.ReorderColumns(
#"Renamed Columns",
{"_airtableRecordId", "_airtableRecordCreatedAt", "_airtableRecordFields"}),
#"Expanded Record Fields" = Table.ExpandRecordColumn(
#"Reordered Columns", "_airtableRecordFields",
Record.FieldNames(#"Reordered Columns"{0}[_airtableRecordFields]),
Record.FieldNames(#"Reordered Columns"{0}[_airtableRecordFields]))
in
#"Expanded Record Fields"

[ray_aramburo]

Yes, the issue isn't on Power Query, it's that the API structure if updated must have placed a limit of records to return in a call. You would need to check the documentation to validate that and see if you can modify the parameter or if it has a threshold (usually if it isn't specified the API takes the default value of the items to return which may or may not be its threshold). If it indeed has a threshold you need to adjust your solution with functions and parameters to address the API pagination. 

[LucieCL]

Thank you for the reply. But I use this code for over a year and it was working untill today.

According to the Airtable API changelog, the latest change was made in November 2024 and it was about this:
Multiple OAuth token refresh requests now only invalidate access if the number of requests exceeds 10 per second

So I do not think that it is about the API connection. Do you have any other idea why this happen?

Thanks!

[ray_aramburo]

Is this a one time issue or have you seen it persist several days? 

[LucieCL]

It is happening since today. Yesterday is was alright.

[ray_aramburo]

I would give it one day to monitor the issue, might be because the API is throttled/has a downtime, because they are updating the API or any other factor. If issue persists, I would start digging deeper. I can't replicate your code due access and permissions but I'll check the documentation to see what I can find.