Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 09, 2019 07:46 AM
From what I can tell, trying several different codes that are supposed to paginate Airtable data in Power BI, some people are getting them to work and some are still just getting 100 records. Has anyone who was just getting 100 records who has now gotten these codes to work describe what change made the code they were using work?
Examples of these codes are found in these threads:
Oct 09, 2019 07:50 AM
Read this from @Oscar_Diaz.
Oct 09, 2019 08:58 AM
Hi @Bill.French,
Thanks for responding. I’ve tried the code in this link, and it’s returning the first 100 records, an offset record, and an error message. Without a better understanding of how it does what it does, I can’t figure out why it’s not working.
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 [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 offset: itr8lU9BsHcWViibW/recUWbGV3DLz7DPS6
The error message: “Web.Contents failed to get contents from ‘https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=itr8lU9BsHcWViibW/recUWbGV3DLz7DPS6’ (404): Not Found”
I was advised to try changing the line after “else” to “Json.Document(Web.Contents(“https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=”&Last_Key, [Headers=[Authorization=“Bearer [KEY_ID]”]]))” which changed the error message to “Web.Contents failed to get contents from ‘https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]&offset=100’ (404): Not Found”
I’ve also tried another code, which yielded a different error message:
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 offset: itrWGE6HxX4Gy6zmP/recUWbGV3DLz7DPS6
The error message: “Web.Contents failed to get contents from ‘https://api.airtable.com/v0/[APP_ID]/[TABLE_ID]?api_key=[KEY_ID]&offset=itrWGE6HxX4Gy6zmP/recUWbGV3D...’ (422): Unprocessable Entity”
I tried a variant here of putting square brackets around Last_Key in the Json.Document line after the “else”. Doing so returned a list with two record values that contained the same first 100 rows and the same offset value, as well as the same 422 error message as above.
These codes are taken from the threads linked in my OP, and they both seem to work for some people but not for others, and I can’t tell what’s causing the difference.
Oct 09, 2019 09:05 AM
There could be many issues in this code, but it’s not well-formatted to reveal issues. Suggest you attempt to inject a degree of logical order in the code through indenting, etc.
In the meantime, I am certain this is one issue:
The ampersand ahead of offset is probably causing an API failure. Suggest you change it to a question mark (?).
Oct 12, 2019 09:13 AM
Hello all,
The code that is giving problems is indeed the dropping of the question mark. Some code that I shared and modified here was written to query not just a table, but a particular VIEW on a table. And I didn’t realize until just recently what was causing people to complain that “the code is not properly paginating”:
Example:
In modifying this code (and attempting to drop the portion that queries a view directly)
... else Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME?view=VIEW_NAME&offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// retrieve results per call
Users accidentally drop the question mark (?) in the code that denotes HTTP request parameters…and unfortunately end up with the following code:
... else Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME&offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// retrieve results per call
When the code they NEED to end up with is as follows:
... else Json.Document(Web.Contents("https://api.airtable.com/v0/AIRTABLE_BASEID/TABLE_NAME?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// retrieve results per call
Oct 14, 2019 02:33 PM
@Matthew_Billiodeaux1, @Bill.French
Thank you both for your help. After further testing, the second code above worked as written. The issue was that after editing the code in query editor from using Source to Pagination, I needed to hit “Refresh Preview”. Once that was done, I could see all the sets of 100 records and expand all of them, instead of just expanding the first one. This was originally not intuitive because usually changing a segment of the code in the query editor causes Power BI to refresh the preview automatically, but that is apparently not the case for this change to where the query is drawn from.
Oct 14, 2019 03:15 PM
Great! Glad to hear you got it working!
Oct 30, 2020 05:09 PM
I was able to get this working with the exception of 2 things:
Oct 14, 2022 01:42 PM
Hello All,
I am not sure if there has been any updates since the last comment. However, I have tried almost all of the ‘Pagination’ codes available online (even the ones can be found here on this page) to fetch more than 100 records from Airtable to Power bi through API and unfortunately none has worked so far.
Has there been any updates to Airtable / Power bi / the code itself that prevents the code from running or may I ask for a favor and someone please provide the FINAL working code.
Thanks in advance.
Oct 16, 2022 05:20 AM
Airtable’s API documentation explains how to loop through more than 100 records.
I don’t know JavaScript, but if you want a no-code way to loop through as many records as you want (up to Airtable’s table limit of 100,000) with no coding at all, then I would recommend using Make.com: