Loading airtable to power bi

I’m attempting to integrate Airtable and Power BI to create reports. Using the API authentication key, I am able to connect to one of my tables in Power BI. However, the JSON string only contains 100 rows when connected.

Airtable and Power BI - mpolino.com I have gone through this blog but still only 100 records are loading.
Is this a limitation ?

this is my query


Pagination=List.Skip(List.Generate(()=> [Page_Key = "init", Counter=0], 
each  [Page_Key] <> null,
each  [Page_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,		  	                       
WebCall  = try if [Counter]<1 then      
      Json.Document(Web.Contents("https://api.airtable.com/v0/mybasekey/tablename",[Headers=[Authorization="Bearer airtablekey"]]))
      Json.Document(Web.Contents("https://api.airtable.com/v0/mybasekey/tablename? offset:""" &[WebCall][Value][offset], [Headers=[Authorization="airtablekey"]])),
      Counter = [Counter]+1
 each [WebCall]

#“Json2Table” = Table.RenameColumns(Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),{{“Column1”, “stepA.1”}}),
#“Expanded stepA.1” = Table.ExpandRecordColumn(Json2Table, “stepA.1”, {“HasError”, “Value”}, {“stepA.1.HasError”, “stepA.1.Value”}),
#“stepA 1 Value” = #“Expanded stepA.1”{0}[stepA.1.Value],
#“Converted to Table” = Record.ToTable(#“stepA 1 Value”),
Value = #“Converted to Table”{0}[Value],
#“Converted to Table1” = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
#“converted to Table1”

Nope - no such limitation.

You might want to read this one and maybe this one.

Hi can you help me with this?
There is more than 100 records in my table…
Is there any other way that might help to load all records in the table to power bi.

Jesney Joseph

No. If you are using the API to gather records, you must use the pagination values to continue to make 100-record requests until all data is made available to the PowerBI connector.

I wish I could, but I currently have 7 projects on my plate and my rate is $250/hr, so unless you have an integration budget and can wait a few days, you probably need to find a consultant or a user with lots of extra time in the community.

1 Like

Not sure if I’m too late to the party, but here is a tutorial I made covering how to do exactly what you are asking: Airtable link to Excel via Power Query

It is complete with pagination code.

Email me if you have questions, but full disclosure, I don’t monitor my person email very closely. (I will however keep an eye open the next few days in case you reply).

Hey @Jesney_Joseph,

Another option is to use the service we built, Sync Inc, to connect to Airtable using PowerBI’s standard Postgres connector.

Here are the full docs - and we’d love to help get you started. Just send us a chat message.