Help

Re: Loading airtable to power bi

1930 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jesney_Joseph
4 - Data Explorer
4 - Data Explorer

Hello,
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

let

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"]]))
 else  
      Json.Document(Web.Contents("https://api.airtable.com/v0/mybasekey/tablename? offset:""" &[WebCall][Value][offset], [Headers=[Authorization="airtablekey"]])),
      Counter = [Counter]+1
      ],
 each [WebCall]
),1),

#“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)
in
#“converted to Table1”

5 Replies 5

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.

Thanks,
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.

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).

Eric_Goldman1
7 - App Architect
7 - App Architect

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.