How to send more than 100 records using API?

[Oscar_Diaz]

I am connected the API of Airtable to Power BI but at the time of displaying the data it only shows me 100 records when the base has more than 1000 records.

[Bill_French]

Oscar_Diaz:

what is the parameter that the offset must pass?

As mentioned by @Andrew_Johnson1 above, the offset value is returned by the first request (which has no offset). Each ensuing request must contain the offset value returned by the recent [previous] successful response.

@Chinara_James makes this abundantly clear in her blog post which was referenced in the forum thread I shared above.

[Oscar_Diaz]

I understand that, what happens is that the visualization of the data I am working with Power BI. If I was working with NodeJs I understand the recommendations but this is not the case.

Example:

• The URL of my database is https://api.airtable.com/v0/Base/Table?api_key=My_Key, when integrating the source of the data in Power BI it only shows me 100 records in Power BI.

[Bill_French]

Oscar_Diaz:

when integrating the source of the data in Power BI it only shows me 100 records in Power BI .

Oscar,

I’m, almost certain PowerBI doesn’t have the internal capacity to automatically understand and perform the multiple requests into Airtable required to get all of the data you want to visualize. As such, you are responsible for creating a middle-ware process that does know how to iterate across all the record sets that the Airtable API is capable of exposing.

There are a few ways to do this - I would check out the Zapier/PowerBI integration recipe.

Oscar_Diaz:

If I was working with NodeJs I understand the recommendations

I wasn’t suggesting you needed to use NodeJS - simply demonstrating how it is done using the Airtable API was my intent recommended this article.

[Bill_French]

Oscar_Diaz:

API of Airtable to Power BI

My fault for not seeing this at the outset - I scanned the threads quickly and when I read this I assumed you were building middleware integration code to use with PowerBI. I should have asked for deeper details.

PowerBI is a wonderful data visualization platform and I have used it often for many clients including this one featured by Microsoft a few years ago when PowerBI started supporting real-time streams. As you can see, I have deep expertise using PowerBI’s API and Azure streaming protocol, so if you need professional help, give me a shout.

[Oscar_Diaz · Accepted solution]

Medium – 19 Sep 19

Conectar datos de Airtable con Power BI

Conectar datos de Airtable con Power BI

Reading time: 3 min read

[Bill_French]

Great article Oscar!

Bill.French:

… creating a middle-ware process that does know how to iterate across all the record sets that the Airtable API is capable of exposing

That’s the middleware you needed and it’s ideal that you did this in PowerBI script - simplifies the moving parts probably fast as well.

[James_Samuel]

Thank you @Oscar_Diaz and @Bill.French

I have almost got there. :slightly_smiling_face: I wrote out the code from the screenshot in the Medium article as below, replacing the [APP_ID] [TABLE_ID] [KEY_ID] with the appropriate entries, but PowerBI coughed at the last line:

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/AIRTABLE_BASEID/TABLE_NAME&offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer key????????????"]])),// retrieve results per call
		Counter = [Counter]+1 // internal counter
	],
	each [Quelle]
),1),
       #"ConvertedTable"= Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)    
 in 
       #"Tipo cambiado"

I closed with the Spanish translation of “tipo cambiado” which is “type changed” and got this error . . .

[Paul_Markovits]

James_Samuel:

I closed with the Spanish translation of “tipo cambiado” which is “type changed” and got this error . . .

Try replacing

“tipo cambiado”

with

#“ConvertedTable”

[Matt_Bryan]

Hi Bill,

RE: Power BI

have a QQ if you don’t mind. i see that you mention experience with Power BI (and you undoubtedly are a Pro with Airtable), so i’m curious if you’ve ran into this issue.

i’m using a slight variant on the web.contents() script listed in this thread and an issue thats popped up is that ‘published’ PowerBI reports have trouble ‘auto-refreshing’ reports/data-sets utilizing web.contents. the tables i’m using in my reports have between 300-25k rows across ~50 columns. i’m trying to deploy the PowerBI App for internal usage without having to do a daily/manual refresh (or something similar).

if you have any suggestions, help would be greatly appreciated. i’ve also checked out [Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code] but it seems to have some sync issues as well, which concerns me deploying using this method.

Best regards,
Matt

[Bill_French]

Matt_Bryan:

if you have any suggestions, help would be greatly appreciated.

Hi Matt - I have a few thoughts but slammed recently. Back shortly.