Airtable and Power BI

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 point it wasn’t doing what I needed from a workflow standpoint. Worldly only works on one device at a time. It doesn’t sync between my iPhone and iPad for example and I’m always worried with upgrades and phone wipes that I’ll wipe out it’s data. (I do back it up to iCloud). Also, you have to enter the number of words you wrote, not your starting and end word counts which means, well…math, potentially after several hours of writing. Still, it’s got a good interface and a decent stats section. I wasn’t unhappy with it, just not happy enough.

I went looking for other options and found Airtable. 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. 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”).

$ curl

  • 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

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.

Once you’ve got Power BI Desktop downloaded, installed and opened:

  • Select Get Data
  • Scroll down and pick Web
  • In the URL box paste the URL from above including the key and click OK
  • If Power BI asks, select Anonymous authentication. It’s not really anonymous, you’ve passed the key in the URL.
    • In Select which level… pick the last choice, it’s the one that includes your table name at the end.
    • Hit Connect

Capture 6

Here is where the JSON format passed by Airtable comes into play. The data in a hierarchy and you’ve got to dig through that to get your data.

  • Find and click on a yellow link labeled List


  • Once that opens you’ll see a list of records

Capture 2

  • Click To Table in the Convert section, upper left.
  • Accept the defaults on the next screen by hitting OK.
  • Click the Split Columns button, upper right in the column header (circled in red below)

Capture 3

  • Accept the defaults by clicking OK on the window that opens
  • Click Split Columns again.

Capture 4

  • Again, click OK to accept the defaults

Capture 5Now you have your data to work with.

  • Click Close & Apply

Once your data is in Power BI, click the Data icon (grid on the left) and then Modeling tab at the top. In here are tools to clean up data types, to change a field from text to numeric for instance. You can also rename columns and do other clean up. I’d do that before building visualizations. You can either build your visualizations here or on the web, but one you are done here, you’ll need to hit Publish in Power BI Desktop to send this to the web.

Also, remember with the free version of Power BI you can set this to update once a day.

This was really painful the first time. I burned a bunch of time getting the connection done and then understanding what I had once it was connected, but now it works and I feel smart.


11 Replies to “Airtable and Power BI”

  1. Thanks Mark, this is awesome! [Eventually Airtable has to drop visualisation/charting features… but until then…]

    I followed your instructions exactly, but PowerBI only imports the first 100 rows of my Airtable. Is this a limitation on the free version of PowerBI?

    1. Correction: turns out the JSON string only contains 100 rows from the Airtable. Strange! Have raised a ticket with Airtable support.

      1. Thanks Richard all of my Airtable tables are personal projects and really small right now. I hadn’t hit 100 records yet. Keep me posted on what Airtable says.

        1. Airtable will only return up to 100 records. If you have more than 100 records on a table, the API will include a field labeled “offset”. You fire off the same url with the offset included to get the next 100 records. Keep doing this until airtable does not return an offset field (meaning all of the records have been returned). Relatively easy to do this using code, but don’t know how to do this in a BI tool like Power BI.

  2. Great writeup. I followed you instructions and I am getting a 404 error when connecting using my https url (“Web.Contents failed to get contents from…..”). Do you have any idea of what’s going on?

  3. I can’t seem to get all my columns of data across – is there a limit? I click on the load more link, but it is still missing some columns. Any ideas?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.