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.