Airtable and Power BI

[Azhar_uddin]

Airtable is really a build your own database app on the web with an iPhone and iPad companion app as well. This means, if I’m writing on my laptop, I don’t have to go find my phone to start a session and if I forget to end it, it’s easy to fix. Now, I just put in the ending word count and it does the rest.

After a lot of fooling around with Excel, Excel Online and Google Sheets, I prefer it to a spreadsheet because I can better control the data entry flow and when I pick a date/time field it just defaults to the current time. This makes it perfect to really get a customized app for exactly what I want.

One downside, I can generate most of the same stats as Wordly, but some of them don’t show on the iOS apps. So I thought I’d throw them into Power BI and build my stats there. Airtable has pretty well documented API. Get best Power BI training online. Power BI connects to API’s, what could go wrong?

Well, apparently I know nothing about connecting JSON to Power BI. Now that I’ve learned, I thought I would put this out as a starting point.

• Create an Airtable table and add a couple of test records.
• In Airtable, click Help > API docs to see API info.
• Select the Authentication section on the left and check the Show API Key box (upper right of the window) to actually see your API Key.
• Highlight and copy the the code under “Example Using Query Parameter” including the API Key.
• Screen Shot 2016-04-29 at 10.48.23 AM
• Paste the code into a text editor. It should look similar to this: (I’ve used a fake API key here, but the Airtable keys do start with the word “key”).
• Remove everything in front of https
• Change “foo” to be your AirTable table name. Not your base name. In my case, the base is Writing Log. That’s identified by the big long app string above. My table is the book name, “Woodbooger”. So the string would be
  https://api.airtable.com/v0/appRJAgF3Qtb4FfZ0/Woodbooger?api_key=key8cGCGEckyc75

Hang on to that.

For the Power BI side, you need to build the Power BI connection in Power BI desktop. You can build reports, dashboards, whatever later on the web, but for now the connection needs to be built in Power BI Desktop.

[Jeremy_Oglesby]

If that is your actual API key in the last bullet point above, you might want to replace it with some placeholder text like YOUR_API_KEY like they do in in the API docs. You don’t want to be displaying your API key publicly like that.

[Jake_Spirek]

How do you deal with Pagination? AirTable limits your results to 100 records at a time. I’m not sure how to get Power BI to do the subsequent API calls (aside from knowing that you need to include the Offset parameter)

[Laurent_Tourneu]

Hi Jake_Spirek
After investigating “Google”, I found this that works perfectly.
You will have to combine each 100 data import together in one.
To do this, edit in PowerBI your query to the AirTable API and adapt the “normal” request between “let” and “in” like this, adding the “Pagination”:

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 NAME>?view=<VIEW_NAME>", [Headers=[Authorization="Bearer YOUR_API_KEY"]]))
        else
            Json.Document(Web.Contents("https://api.airtable.com/v0/<APP-ID>/<TABLE NAME>?view=<VIEW_NAME>&offset="&Last_Key, [Headers=[Authorization="Bearer YOUR_API_KEY"]])), // retrieve results per call
            Counter = [Counter]+1 // internal counter
        ],
        each [Quelle]
        ),1),
 
	.....
	.....

in

Best
Laurent

[Steve_Edwards]

Hi @Laurent_Tourneux

Would you mind please sharing the whole query from above as i’m looking to do something similar?

Thanks.

Steve

[Ivan_Larson]

Hi @Laurent_Tourneux,

Thank you for posting this code! I’ve tried using it and I’m getting some errors. My 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)

When I do this, I get a list with two records. Expanding the first one yields the first 100 rows of data, as well as an offset value: itrWGE6HxX4Gy6zmP/recUWbGV3DLz7DPS6. Expanding the second record yields 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

[Laurent_Tourneu]

Good evening
Due to the pagination issue with PowerBI, i decided to move to GDS, in relation with Google Sheet as source using the “AirTable Importer by Railsware” plugin.
It works fine for me.
Sorry, but I didn’t go deeper on this topic.
Best Regards
Laurent

[Matthew_Billio1]

Hello @Ivan_Larson, @Laurent_Tourneux, @Jake_Spirek, and @Steve_Edwards,

@Azhar_uddin’s post is a great start. I would never have thought of including that detail in a tutorial. As a supplement to his post, you can see completed code here and a few more practical steps to connecting excel/powerbi and airtable:

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 …

[Cody_Winchester]

@Laurent_Tourneux, this code produces, “Expression.SyntaxError: Token Identifier expected.” What update is required? Thanks.