Airtable and Power BI


#1

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.


#2

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.