The Airtable API uses the concept of pagination.


The response will contain an offset if there are more than 100 records. Include this offset in the next request’s parameters

How can I add these parameters?

Are you using the Airtable Javascript library or accessing the it via their REST API?

If you are using the REST API you need to pass another parameter “offset”

I am using REST API. With what value should I pass the “offset” parameter?

The “offset” value is returned in the response of your first call to the Airtable API under the key “offset”


Take the above returned “offset” value and pass it in your new request.

This is what you want - API Offset Parameter

Reloading the data source sending as an offset parameter and still showing 100 records. The image I send is using POSTMAN to return the json.

@Oscar_Diaz,

In the screenshot shown, there is no value shown for the offset parameter, therefore, the API will default to the first 100 records. It is responding exactly how it should.

@Bill.French That the point, what is the parameter that the offset must pass?

As mentioned by @Andrew_Johnson1 above, the offset value is returned by the first request (which has no offset). Each ensuing request must contain the offset value returned by the recent [previous] successful response.

@Chinara_James makes this abundantly clear in her blog post which was referenced in the forum thread I shared above.

I understand that, what happens is that the visualization of the data I am working with Power BI. If I was working with NodeJs I understand the recommendations but this is not the case.

Example:

• The URL of my database is https://api.airtable.com/v0/Base/Table?api_key=My_Key, when integrating the source of the data in Power BI it only shows me 100 records in Power BI.

Oscar,

I’m, almost certain PowerBI doesn’t have the internal capacity to automatically understand and perform the multiple requests into Airtable required to get all of the data you want to visualize. As such, you are responsible for creating a middle-ware process that does know how to iterate across all the record sets that the Airtable API is capable of exposing.

There are a few ways to do this - I would check out the Zapier/PowerBI integration recipe.

I wasn’t suggesting you needed to use NodeJS - simply demonstrating how it is done using the Airtable API was my intent recommended this article.

My fault for not seeing this at the outset - I scanned the threads quickly and when I read this I assumed you were building middleware integration code to use with PowerBI. I should have asked for deeper details.

PowerBI is a wonderful data visualization platform and I have used it often for many clients including this one featured by Microsoft a few years ago when PowerBI started supporting real-time streams. As you can see, I have deep expertise using PowerBI’s API and Azure streaming protocol, so if you need professional help, give me a shout.

Great article Oscar!

That’s the middleware you needed and it’s ideal that you did this in PowerBI script - simplifies the moving parts probably fast as well.

Thank you @Oscar_Diaz and @Bill.French

I have almost got there. I wrote out the code from the screenshot in the Medium article as below, replacing the gAPP_ID] PTABLE_ID] EKEY_ID] with the appropriate entries, but PowerBI coughed at the last line:

let


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 rCounter]<1 then "" else  Quelle]uValue]Voffset] otherwise null, // determine the LastKey for the next execution


	Quelle= try if rCounter]<1 then


		Json.Document(Web.Contents("https://api.airtable.com/v0/oAPP_ID]/PTABLE_ID]", IHeaders=aAuthorization="Bearer eKEY_ID]"]]))





	else  


		Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME&offset="&"WebCall]bValue]Voffset] , eHeaders=aAuthorization="Bearer key????????????"]])),// retrieve results per call


		Counter = tCounter]+1 // internal counter


	],


	each 	Quelle]


),1),


       #"ConvertedTable"= Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)    


 in 


       #"Tipo cambiado"

I closed with the Spanish translation of “tipo cambiado” which is “type changed” and got this error . . .

Try replacing

“tipo cambiado”

with

#“ConvertedTable”

Hi Bill,

RE: Power BI

have a QQ if you don’t mind. i see that you mention experience with Power BI (and you undoubtedly are a Pro with Airtable), so i’m curious if you’ve ran into this issue.

i’m using a slight variant on the web.contents() script listed in this thread and an issue thats popped up is that ‘published’ PowerBI reports have trouble ‘auto-refreshing’ reports/data-sets utilizing web.contents. the tables i’m using in my reports have between 300-25k rows across ~50 columns. i’m trying to deploy the PowerBI App for internal usage without having to do a daily/manual refresh (or something similar).

if you have any suggestions, help would be greatly appreciated. i’ve also checked out eUsing The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code] but it seems to have some sync issues as well, which concerns me deploying using this method.

Best regards,


Matt

Hi Matt - I have a few thoughts but slammed recently. Back shortly.

One of the key things that dashboard and data science integrators have become increasingly responsible for is transitioning data into viz-ready form. This is really the ETL aspect of data preparedness and while ETL is the bog-boy label for this activity, the trends show that in a more democratized IT climate, people like you and me get to resolve at least some of these challenges.

I have written about report-ready and viz-ready processes. Expecting PowerBI (which is really the last mile of data visualization), we often expect it to bring some ETL magic when in reality, it is really designed to abstract data summations into it’s report designer and/or Microsoft Azure.

It’s not surprising that PowerBI is unable to auto-refresh the data and deliver it in a timely fashion. It is a client that expects robust back-end machinery that is dependent on an API (the Airtable API) that has some known weaknesses such as five API calls per second. It’s very possible that the underlying interface has no notion of the throttling that might be needed.

Have you tried to throttle the script process to see if the updates become more reliable?

Another test you try is to simply export the data from Airtable and place it into a Google sheet and then create a PowerBI connector to that source just to know if (a) the script performs well, and (b) the data updates swiftly and reliably. If it does, you have likely narrowed the issue to the general idea of directly leaning on the Airtable API.

In almost 80% of cases involving Airtable data in reporting and visualization processes, I cache-forward data on a timed process that insulates Airtable from other systems because I run into so many issues like the one you are describing. This is not always ideal because it introduces slightly additional latency to the data visualizations. But if the tolerances are reasonable (minimum 60 seconds and reasonably more like five or ten minutes), this is a smart pathway.

Hi Oscar, I am facing same problem since i can’t get more than 100 records. could you share with how you could solve it. i am managing pagination in frontend “next js” based on total records from airtable. request is POST request to node express server.

Here’s the correct script for pagination:

let


Pagination = List.Skip(List.Generate( () => ;Last_Key = “init”, Counter=0],


each 
Last_Key] <> null,


each 
Last_Key = try if rCounter]<1 then “” else �Quelle]eValue]loffset] otherwise null,


Quelle= try if eCounter]<1 then


Json.Document(Web.Contents(“https://api.airtable.com/v0/aput base id here]/sput table number here]?api_key=rput api key here]&view=eput view name here]”))


else


Json.Document(Web.Contents(“https://api.airtable.com/v0/aput base id here]/tput table number here]?api_key=rput api key here]&view= put view name here]&offset=”&Last_Key)),


Counter = >Counter]+1


],


each 

),1),


#“Convert Table” = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)





in #“Convert Table”

Replace all of the code you see in Advanced Editor with this snippet. Paste your base id, table id and view name instead of the placeholders.

For everyone struggling to find the correct script, like I just did. (Because I couldn’t find one definitive correct version anywhere)