Airtable, API, Power BI, and over 100 records

Ivan_Larson · ‎Oct 09, 2019

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:

Bill_French · ‎Oct 09, 2019

Read this from @Oscar_Diaz.

Ivan_Larson · ‎Oct 09, 2019

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/recUWbGV3D...’ (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.

Bill_French · ‎Oct 09, 2019

@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:

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

Matthew_Billio1 · ‎Oct 12, 2019

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

Ivan_Larson · ‎Oct 14, 2019

@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.

Matthew_Billio1 · ‎Oct 14, 2019

Great! Glad to hear you got it working!

stollerd · ‎Oct 30, 2020

I was able to get this working with the exception of 2 things:

I cannot figure out how to bring in the Record ID for each row. Is there something I need to enable on the Airtable side or is it something in the code?
For my columns that are lookups, I’m getting [List] in my results in Excel. Any help there is appreciated.

Shahab_Narimani · ‎Oct 14, 2022

Hello All,
I am not sure if there has been any updates since the last comment. However, I have tried almost all of the ‘Pagination’ codes available online (even the ones can be found here on this page) to fetch more than 100 records from Airtable to Power bi through API and unfortunately none has worked so far.

Has there been any updates to Airtable / Power bi / the code itself that prevents the code from running or may I ask for a favor and someone please provide the FINAL working code.
Thanks in advance.

ScottWorld · ‎Oct 16, 2022

Airtable’s API documentation explains how to loop through more than 100 records.

I don’t know JavaScript, but if you want a no-code way to loop through as many records as you want (up to Airtable’s table limit of 100,000) with no coding at all, then I would recommend using Make.com: