[GUIDE] Fetch More Than 100 Records In Power BI Via AirTable API
AirTable is a great platform for maintaining a personal cloud database without any knowledge of servers and programming. Its best feature is that it can easily be connected via PowerBI.
AirTable To Power BI Hurdle
The biggest hurdle which its consumers face is the API restriction. Airtable’s API can fetch only 100 entries in PowerBI after connection and uses pagination. This is what it is designed for. People have tried customizing the structures and standard Macro Scripts to fetch all the entries of their table at once but of no use.
Being a programmer, I’ve worked on many support projects of Airtables where the clients requested for custom Macro Code to fetch more than 100 records in PowerBI. I’ve seen many forums and Airtable support community but nobody has able to help and they simply reject the argument with a request to comply with the Airtable’s API documentation.
But that’s not the case in reality. Everything is possible with programming especially when communicating via API requests. But the programmers have to test each scenario and come up with a solution that actually works. That is why they charge money for their time and effort, same as I do.
So here is the piece of code which can fetch more than 100 records in Power BI via Airtable API. I’ll describe how it works:
Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Initialize Values
each [Last_Key] <> null, // Condition
each [Last_Key = try if [Counter]<1 then "" else [Quelle][Value][offset] otherwise null, // Determine Values
Quelle= try if [Counter]<1 then
Json.Document(Web.Contents("https://api.airtable.com/v0/appqRdDk5cUYPccP9/My%20First%20Table?api_key=keydBJnWku3VeUTIG&view=Base&offset="&Last_Key)), // Script
Counter = [Counter]+1 // Counter
#"Convert Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
Here is the format:
[YOUR TABLE API FULL URL] Then Put Question Mark ? Then Put Your API Key like:
[api_key=keydBUTKFKFOFMOWolp] Then Put & Sign and view=Base
So finally, it should look like this, with your own data:
Secondly, after ELSE, it will be like:
NOTE: All this happens between the inverted commas. The rest remains the same.
I hope this solves your issue. If you have any questions, let me know in the comments.
Don’t forget to subscribe to our YouTube Channel.
If you have any website project, let me know via contact form on Contact Us Page.