JSON string only contains 100 rows when connected to Power BI Desktop

[Laura_Viar]

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:

mpolino.com – 29 Apr 16

Airtable and Power BI - mpolino.com

Warning, this may be long and boring, but I need to write it down. The next Lizard Wong book is finally, finally, moving along nicely. For a while I’ve tracked my writing and word count using Wordly, an iPhone app. It works well enough, but at some...

Any ideas for resolving this?

Thanks! - Meg

[Tuur]

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.

[Tom_Luttikhuis]

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

[Tuur]

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:

[Tuur]

BTW: there are other posts here that might help you…

For example:

Automatically fetch all pages? API

Apologies if this is an ignorant question, but I want my call to the API to fetch all pages automatically, without extra user action. I started with the sample code base('allocations').select({ // Selecting the first 3 records in Main View: maxRecords: 3, view: "Main View" }).eachPage(function page(records, fetchNextPage) { // This function (`page`) will get called for each page of records. records.forEach(function(record) { console.log('Retrieved ', record.get('ID')); }); // To fetch…

Moving Airtable to SQL API

Another quick question: one the rare occasion (it has happened several times) that over 100 orders come in at once. Is there a conditional parameter to pass into the API call (similar to Maxrequest,etc.) that would return 100 records --> ‘wait five seconds’ --> and then re-query any remaining records? (I’m guessing this would have to be done server-side)

List Records without Pagination? API

I’m not a Python developer, but looking at their code examples (specifically the Twitter handler) this seems like it might get close https://raw.githubusercontent.com/damiendallimore/SplunkModularInputsPythonFramework/master/implemen... class AirTableEventHandler: def __init__(self,**args): pass def __call__(self, response_object,raw_response_output,response_type,req_args,endpoint): if response_type == "json": output = json.loads(r…

[Chester_McLaugh]

Here’s the best my Google-foo could find. It appears to be a non-negligible tasks.

community.powerbi.com – 22 Feb 16

how to create a query that paginates?

I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the results.   For instance, if you send:...

community.powerbi.com – 5 Jul 16

JSON API - How to do pagination?

I am looking to get all the tasks from Teamwork API http://developer.teamwork.com/todolistitems#retrieve_all_task but I can only get 250 at a time - I need to use pagination to get the rest.    How can I build a PowerQuery that can use...

[Mo_re]

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.

[Victoria_Plumme]

Thank you! This helped me so much!

[Varun_Jain]

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=itrb129iLnjMud...’ (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!

[Jani_Green]

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!