Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ivan_larson_cki
Helper III
Helper III

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 applying that formula to my data, but something is going wrong.

 

Here's the formula as I've typed it into the Advanced Editor (substituting [APP_ID], [TABLE_ID], and [KEY_ID] for my particular IDs:

 

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 [Quelle][Value][offset] otherwise null, // determine the LastKey for the next execution
          Quelle= try if [Counter]<1 then
          Json.Document(Web.Contents("https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]", [Headers=[Authorization="Bearer [KEY_ID]"]]))
     else
          Json.Document(Web.Contents("https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset="&Last_Key, [Headers=[Authorization="Bearer [KEY_ID]"]])), // retrieve results per call
          Counter = [Counter]+1 // internal counter
     ],
     each [Quelle]
     ),1),

 

The result in the query editor looks like this:

Airtable Records.png

Each of these records is a table, the first consisting of HasError = FALSE and a record containing my first hundred rows. The second, where HasError = TRUE, returns a an error with Reason: DataSource.Error and message "Web.Contents failed to get contents from 'https://api.airtable.com/v0/[App_ID]/[TABLE_ID]&offset=itr8lU9BsHcWViibW/recUWbGV3DLz7DPS6' (404): Not Found"

 

Can anyone illuminate what error I'm making in my use of this Pagination code?

1 ACCEPTED SOLUTION
ivan_larson_cki
Helper III
Helper III

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.

View solution in original post

7 REPLIES 7
Jawed
Helper III
Helper III

Hi everyone,

I am not a technical person so I have trouble even getting the API in the first place. Is there anywhere that I can not only use the useful code you guys finally fixed here but also to know how to do the whole process in a step by step instruction? 

I would be really grateful.

Thanks

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.

Thank you very much, @ivan_larson_cki 

ivan_larson_cki
Helper III
Helper III

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.

v-lid-msft
Community Support
Community Support

Hi @ivan_larson_cki ,

 

Based on my research, the offset parameter seem like a  number value, could you please try to test with following formula and see  if it can return without the same error?

 

Json.Document(Web.Contents("https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset="&"100", [Headers=[Authorization="Bearer [KEY_ID]"]]))

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for your response. I edited the second URL to the formula you specified, and it changed the error message to the following:

"Web.Contents failed to get contents from 'https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=100' (404): Not Found"

 

I tried these variations with the same error message:

Json.Document(Web.Contents("https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=100", [Headers=[Authorization="Bearer [KEY_ID]"]]))
Json.Document(Web.Contents("https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=100"&[Last_Key], [Headers=[Authorization="Bearer [KEY_ID]"]]))

 

This variant:

Json.Document(Web.Contents("https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=100"&Last_Key, [Headers=[Authorization="Bearer [KEY_ID]"]]))

changed the error message to the following:

"Web.Contents failed to get contents from 'https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=100itr8lU9BsHcWViibW/recUWbGV3DLz7DPS6' (404): Not Found"

Hi @ivan_larson_cki ,

 

We think the problem is the there should be a "?" between URL and the parameter, and "&"Between multi parameter, I am sorry for  did not notice it in previous post, Could you please try the following function to get your data?

 

Json.Document(Web.Contents("https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]?offset="&[Last_Key], [Headers=[Authorization="Bearer [KEY_ID]"]]))

 

And your parameter should be URL encoded, such as replace "/" into %2F, please refer to the following wiki for more information about URL encode : https://en.wikipedia.org/wiki/Percent-encoding

 

https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]?offset=100itr8lU9BsHcWViibW%2FrecUWbGV3DLz7DPS6

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.