Hello,
I’m attempting to integrate Airtable and Power BI to create reports. Using the API authentication key, I am able to connect to one of my tables in Power BI. However, the JSON string only contains 100 rows when connected.
A more complete explanation of this issue is covered in this short tutorial and subsequent comments:
Any ideas for resolving this?
Thanks! - Meg
+4Hi Meg,
The API only gives you 100 rows by default. This is from the docs:
Pagination
The server returns one page of records at a time. Each page will contain pageSize records, which is 100 by default.
If there are more records, the response will contain an offset. To fetch the next page of records, include offset in the next request’s parameters.
Iteration may timeout due to client inactivity or server restarts. In that case, the client will receive a 422 response with error message LIST_RECORDS_ITERATOR_NOT_AVAILABLE. It may then restart iteration from the beginning.
Hi Meg,
The API only gives you 100 rows by default. This is from the docs:
Pagination
The server returns one page of records at a time. Each page will contain pageSize records, which is 100 by default.
If there are more records, the response will contain an offset. To fetch the next page of records, include offset in the next request’s parameters.
Iteration may timeout due to client inactivity or server restarts. In that case, the client will receive a 422 response with error message LIST_RECORDS_ITERATOR_NOT_AVAILABLE. It may then restart iteration from the beginning.
Hi Tuur,
Can you give me a step by step approach as to solving this issue.
I am also facing the following problem. I have connected Power BI to my Airtable CRM via the JSON API. Now only 100 records are displayed. Which steps do I take in order to be able to view the addition ±300 items in Power BI?
Thanks a lot.
Best,
Tom
+4Hi Tuur,
Can you give me a step by step approach as to solving this issue.
I am also facing the following problem. I have connected Power BI to my Airtable CRM via the JSON API. Now only 100 records are displayed. Which steps do I take in order to be able to view the addition ±300 items in Power BI?
Thanks a lot.
Best,
Tom
Hi @Tom_Luttikhuis!
Unfortunately I don’t have a clue how that works with Power BI. In my requests I simply add the offset. That would be something like adding ‘&offset=XXX’ to the request URL. You’ll find the correct offset in the JSON with the first 100 records.
Hope this helps. :slightly_smiling_face:
+4Hi Tuur,
Can you give me a step by step approach as to solving this issue.
I am also facing the following problem. I have connected Power BI to my Airtable CRM via the JSON API. Now only 100 records are displayed. Which steps do I take in order to be able to view the addition ±300 items in Power BI?
Thanks a lot.
Best,
Tom
BTW: there are other posts here that might help you…
For example:
Hi Tuur,
Can you give me a step by step approach as to solving this issue.
I am also facing the following problem. I have connected Power BI to my Airtable CRM via the JSON API. Now only 100 records are displayed. Which steps do I take in order to be able to view the addition ±300 items in Power BI?
Thanks a lot.
Best,
Tom
Here’s the best my Google-foo could find. It appears to be a non-negligible tasks.
Hello! Little bit late to the party, but I got Power BI to fetch the paginated Airtable pages. What has not been implemented yet is a timout to avoid sending too many requests. Here’s the “Power Query” you’ll need:
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 [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>&offset="&Last_Key&"")), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1)
in
Pagination
I got help in finding the solution here.
Hello! Little bit late to the party, but I got Power BI to fetch the paginated Airtable pages. What has not been implemented yet is a timout to avoid sending too many requests. Here’s the “Power Query” you’ll need:
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 [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>&offset="&Last_Key&"")), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1)
in
Pagination
I got help in finding the solution here.
Thank you! This helped me so much!
Hello! Little bit late to the party, but I got Power BI to fetch the paginated Airtable pages. What has not been implemented yet is a timout to avoid sending too many requests. Here’s the “Power Query” you’ll need:
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 [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>&offset="&Last_Key&"")), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1)
in
Pagination
I got help in finding the solution here.
Hello Mo_re
You seem to have closest to solving the pagination problem with Airtable’s API and using it successfully in Power BI.
This is exactly what I’m trying to do, albeit unsuccessfully.
I have used your code, and changed it to reflect the table and API details. It is successfully pulling the first 100 records, but then there is a second block which throws up an error:
Web.Contents failed to get contents from ‘https://api.airtable.com/v0/app9KuDCprvz0KkC4/Orders?api_key=keyfCT5EWGzVJDv9n&offset=itrb129iLnjMudith/rec2gTOZzD7WqVKCC’ (422): Unprocessable Entity
I’d really like to use your help on this. Don’t know if personal info is permitted here, but could you send me a PM, so that I could use your help?
Thanks!
Hi, did anyone find a solution to the unprocessable error please? I can test the string without offset straight in a browser window and get the desired (unformatted) response but when i add the offset i get "{“error”: “type”:“LIST_RECORDS_ITERATOR_NOT_AVAILABLE”}}
Thanks!
Hello! Little bit late to the party, but I got Power BI to fetch the paginated Airtable pages. What has not been implemented yet is a timout to avoid sending too many requests. Here’s the “Power Query” you’ll need:
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 [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>&offset="&Last_Key&"")), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1)
in
Pagination
I got help in finding the solution here.
This code still works like a charm with a few updates.
Replace api with you Airtable API and remove endpoint as that had an error in PowerBi.
I don’t know how to code but I just deleted random parts of the code until it worked. PowerBi now shows all my rows to appear in PowerBi so thanks so much!
Not sure if this is helpful for anyone on this thread anymore, but for what it is worth, here is a nearly complete tutorial on connecting excel to airtable that I wrote up a while back (you can either click the link to view it or expand it here by clicking the drop down arrow on the top right of the excerpt):
You can see the complete code here:
Also, here is a write up of how you may run into some issues when using this code:
Not sure if this is helpful for anyone on this thread anymore, but for what it is worth, here is a nearly complete tutorial on connecting excel to airtable that I wrote up a while back (you can either click the link to view it or expand it here by clicking the drop down arrow on the top right of the excerpt):
You can see the complete code here:
Also, here is a write up of how you may run into some issues when using this code:
Thanks all for the help. One more note, if you follow all the codes and tips and still seeing missing columns, make sure all the cells in your first row have data in them, even just filler data; otherwise, the specific column might won’t show up in powerbi.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
