How to send more than 100 records using API?

[Oscar_Diaz]

I am connected the API of Airtable to Power BI but at the time of displaying the data it only shows me 100 records when the base has more than 1000 records.

[Bill_French]

Matt_Bryan:

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

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.

[Atef_Mounir]

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.

[Alex_Fedosenko]

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 [Counter]<1 then “” else [Quelle][Value][offset] otherwise null,
Quelle= try if [Counter]<1 then
Json.Document(Web.Contents(“https://api.airtable.com/v0/[put base id here]/[put table number here]?api_key=[put api key here]&view=[put view name here]”))
else
Json.Document(Web.Contents(“https://api.airtable.com/v0/[put base id here]/[put table number here]?api_key=[put api key here]&view=[put view name here]&offset=”&Last_Key)),
Counter = [Counter]+1
],
each [Quelle]
),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)