Forum Discussion

ivan_larson_cki's avatar
ivan_larson_cki
Icon for Helper III rankHelper III
7 years ago
Solved

Trouble with Pagnation for Airtable

Airtable will only let an API pull data from 100 records at a time. I've searched around and found a single formula that keeps getting re-posted to circumvent the 100-record limit. I've tried applyin...
Need Help
  • ivan_larson_cki's avatar
    ivan_larson_cki
    7 years ago

    Resolved using this code:

     

     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/<APP_ID>/<TABLE_ID>?api_key=<KEY_ID>")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<APP_ID>/<TABLE_ID>?api_key=<KEY_ID>&offset="&Last_Key)), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
    ),1)

    The issue was not so much the exact phrasing, but rather that after editing the original "Source" code that only pulled 100 records to a Pagination code, I needed to hit "Refresh Preview" so that the full set of paginated data segments appeared. Only then was it possible to tell the query to open all of them. Before that, it was pulling all the data, but just not showing it correctly in the preview, which led to me not knowing to fix the command after the Pagination command.

ivan_larson_cki's avatar
ivan_larson_cki
Icon for Helper III rankHelper III
7 years ago

Hi Jawed,

 

Having never worked with custom sourcing before, I found this thread on the Airtable forum useful:

https://community.airtable.com/t/airtable-and-power-bi/12157

 

The important info you need to know are your Airtable App ID, the name of the table you want to access, and the Key ID tied to your airtable username. If you click "Help" from your Airtable app, select API Documentation, go to the Authentication section, and check the API key checkbox in the top right, you should be able to see the App ID and the Key ID. Then, in the Advanced Editor in Power BI's Query Editor, you'll need to copy in the solution in this thread as the first step (no Source step!). Once you run the query with that step, you'll need to click a few options in Power Query to expand your table a few different ways until you finally get to the actual data. At that point, as long as you can verify that the number of rows matches your Airtable table, you should be set.

 

Also, this only works in PBI Desktop. Airtable pagination does not work in PBI Service.

Jawed's avatar
Jawed
Icon for Helper III rankHelper III
7 years ago

Thank you very much, ivan_larson_cki