Airtable, API, Power BI, and over 100 records

From what I can tell, trying several different codes that are supposed to paginate Airtable data in Power BI, some people are getting them to work and some are still just getting 100 records. Has anyone who was just getting 100 records who has now gotten these codes to work describe what change made the code they were using work?

Examples of these codes are found in these threads:



Read this from @Oscar_Diaz.

1 Like

Hi @Bill.French,

Thanks for responding. I’ve tried the code in this link, and it’s returning the first 100 records, an offset record, and an error message. Without a better understanding of how it does what it does, I can’t figure out why it’s not working.

Code:
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 [Quelle][Value][offset] otherwise null, // determine the LastKey for the next execution
Quelle= try if [Counter]<1 then
Json.Document(Web.Contents(“https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]”, [Headers=[Authorization=“Bearer [KEY_ID]”]]))
else
Json.Document(Web.Contents(“https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=”&Last_Key, [Headers=[Authorization=“Bearer [KEY_ID]”]])), // retrieve results per call
Counter = [Counter]+1 // internal counter
],
each [Quelle]
),1)

The offset: itr8lU9BsHcWViibW/recUWbGV3DLz7DPS6

The error message: “Web.Contents failed to get contents from ‘https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=itr8lU9BsHcWViibW/recUWbGV3DLz7DPS6’ (404): Not Found”

I was advised to try changing the line after “else” to “Json.Document(Web.Contents(“https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=”&Last_Key, [Headers=[Authorization=“Bearer [KEY_ID]”]]))” which changed the error message to “Web.Contents failed to get contents from ‘https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=100’ (404): Not Found”

I’ve also tried another code, which yielded a different error message:

Code:
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/[APP_ID]/[TABLE_ID]?api_key=[KEY_ID]”)) else Json.Document(Web.Contents(“https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]?api_key=[KEY_ID]&offset="&Last_Key&"”)), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1)

The offset: itrWGE6HxX4Gy6zmP/recUWbGV3DLz7DPS6

The error message: “Web.Contents failed to get contents from ‘https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]?api_key=[KEY_ID]&offset=itrWGE6HxX4Gy6zmP/recUWbGV3DLz7DPS6’ (422): Unprocessable Entity”

I tried a variant here of putting square brackets around Last_Key in the Json.Document line after the “else”. Doing so returned a list with two record values that contained the same first 100 rows and the same offset value, as well as the same 422 error message as above.

These codes are taken from the threads linked in my OP, and they both seem to work for some people but not for others, and I can’t tell what’s causing the difference.

@Ivan_Larson,

There could be many issues in this code, but it’s not well-formatted to reveal issues. Suggest you attempt to inject a degree of logical order in the code through indenting, etc.

In the meantime, I am certain this is one issue:

image

The ampersand ahead of offset is probably causing an API failure. Suggest you change it to a question mark (?).

1 Like

Hello all,

The code that is giving problems is indeed the dropping of the question mark. Some code that I shared and modified here was written to query not just a table, but a particular VIEW on a table. And I didn’t realize until just recently what was causing people to complain that “the code is not properly paginating”:

  • some users of the code would try to modify the code to query the table directly, and overlook the importance of the question mark / accidentally delete it from their code.

Example:

In modifying this code (and attempting to drop the portion that queries a view directly)

...
     else  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME?view=VIEW_NAME&offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// retrieve results per call

Users accidentally drop the question mark (?) in the code that denotes HTTP request parameters…and unfortunately end up with the following code:

...
	else  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME&offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// retrieve results per call

When the code they NEED to end up with is as follows:

...
	else  Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// retrieve results per call

@Matthew_Billiodeaux1, @Bill.French

Thank you both for your help. After further testing, the second code above worked as written. The issue was that after editing the code in query editor from using Source to Pagination, I needed to hit “Refresh Preview”. Once that was done, I could see all the sets of 100 records and expand all of them, instead of just expanding the first one. This was originally not intuitive because usually changing a segment of the code in the query editor causes Power BI to refresh the preview automatically, but that is apparently not the case for this change to where the query is drawn from.

2 Likes

Great! Glad to hear you got it working!