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.
- 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 https://api.airtable.com/v0/appRJAgF3Qtb4FfZ0/foo?api_key=key8cGCGEckyc75
- 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
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
- 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)
- Accept the defaults by clicking OK on the window that opens
- Click Split Columns again.
- Again, click OK to accept the defaults
- 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.