JSON string only contains 100 rows when connected to Power BI Desktop

[Laura_Viar]

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.

A more complete explanation of this issue is covered in this short tutorial and subsequent comments:

mpolino.com – 29 Apr 16

Airtable and Power BI - mpolino.com

Warning, this may be long and boring, but I need to write it down. The next Lizard Wong book is finally, finally, moving along nicely. For a while I’ve tracked my writing and word count using Wordly, an iPhone app. It works well enough, but at some...

Any ideas for resolving this?

Thanks! - Meg

[Jake_Spirek]

@Mo_re thank you so much for that snippet. Worked for me, and saved me a ton of time in Power BI!

[Kendall_Shortt]

This code still works like a charm with a few updates.

Replace api with you Airtable API and remove endpoint as that had an error in PowerBi.

I don’t know how to code but I just deleted random parts of the code until it worked. PowerBi now shows all my rows to appear in PowerBi so thanks so much!

[Matthew_Billio1]

Not sure if this is helpful for anyone on this thread anymore, but for what it is worth, here is a nearly complete tutorial on connecting excel to airtable that I wrote up a while back (you can either click the link to view it or expand it here by clicking the drop down arrow on the top right of the excerpt):
You can see the complete code here:

Airtable link to Excel via Power Query Show and tell

Here is a way to automatically pull information from airtable into Excel via power-Query using a bit of coding. If you are looking to just copy and paste the code you need without much explanation, ignore the details :slight_smile:

Details

SECTION 1- CONNECTING EXCEL TO POWERQUERY- The first issue a user may run into while trying to get data to automatically feed into Excel is creating an authenticated connection to Airtable from Excel’s powerQuery. The following code will be used in a later …

Also, here is a write up of how you may run into some issues when using this code:

Airtable link to Excel via Power Query Show and tell

We have solved one part of the above issues with refreshing, which is to convert all query parameters to Query options in Json.Document(Web.Contents()). So instead of tacking on “?view=MyView”, add Query=[view=“MyView”] to the options array Json.Document(Web.Contents("https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]", [Headers=[Authorization="Bearer keyXXXXXXXXXXXXXX"],Query=[view="MyView"]])) That was thanks to this url: http://blog.datainspirations.com/2018/02/17/dyna…

Airtable link to Excel via Power Query Show and tell

Thanks for providing this info. I was getting a “[null]” result in my query and wanted to share the fix if anyone else experiences the same. I was using the AIRTABLE_BASEID found in the URL address bar in the browser. I had to go to the Airtable API page and use the BASE_ID shown in the connection string Authentication section. It was different than the one in the URL address bar when accessing the base. Once I replaced that, the [null] record issue went away and I was able to see the list of…

Airtable link to Excel via Power Query Show and tell

Hello @Adolfo_Ferreira and @Ivan_Larson , I am back from my “siesta”. After reviewing code that was shared with me, I found the error. If you are not using a particular view, then you had to modify the code, deleting the VIEW from the http request. When deleting the portion of the code that pertains to a parcticular airtable view, be careful NOT to delete the question mark (?) in the code that denotes HTTP request parameters… This is easy to miss (as seen by the fact that I missed it when …

[SYzzzz]

Thanks all for the help. One more note, if you follow all the codes and tips and still seeing missing columns, make sure all the cells in your first row have data in them, even just filler data; otherwise, the specific column might won’t show up in powerbi.