Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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?
Solved! Go to Solution.
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.
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.
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.
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.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |