airtable to power bi fetch more than 100 entries

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

Seeking Support

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.

Solution

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:

let
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"))
else
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
],
each [Quelle]
),1),
#"Convert Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Type Changed"

 

Explanation

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:

https://api.airtable.com/v0/appqRdDk5cUYPccP9/My%20First%20Table?api_key=keydBJnWku3VeUTIG&view=Base

Secondly, after ELSE, it will be like:
https://api.airtable.com/v0/appqRdDk5cUYPccP9/My%20First%20Table?api_key=keydBJnWku3VeUTIG&view=Base&offset=

NOTE: All this happens between the inverted commas. The rest remains the same.

Video Solution

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.

Leave a comment