Finding things to publish online for public consumption is something I’m often a bit reluctant to do. Most of my work is for customers, and there’s no way I’m going to share some of their data unless they’ve explicitly allowed it.
So when Jorge Segarra (@sqlchicken) posted a challenge to publish a Power BI report on the web for T-SQL Tuesday, I had give some thought about what kind of data to show. Luckily, Scott Stauffer (@sqlsocialite) has been Fitbit-challenging me over recent weeks, and in particular, in some that don’t include some of the big-steppers like Steve Stedman (@sqlemt), who should probably change his name to Stepman, considering he has a treadmill desk and does over 100k each week. Anyway – with a group of people who do the same order of magnitude of steps as me, I stretched myself to do better than I had been doing, and figured this could make useful data.
I started with an export of the last 31 days from Fitbit. That’s as much data as you can pull down from them, and although I could go to the effort of getting extra exports and combining them, I didn’t for this. After all, I’d rather be out getting more steps done than analysing them.
I had a bit of cleaning to do first, because the data had an annoying first line. In fact, I found it easier to pull the data in as text, remove the top line, then split the data up by the delimiter. I could then mark the various columns as numbers, which made life a lot easier.
After all this was done, I was ready to throw some stuff onto a report (ok, I also added some measures to show the steps as a percentage of the total steps – they have to be measures to handle the division properly). It was easy to get a chart on there, and a card to show some of the numbers. But I wanted to make it a bit more interesting… so I added an average, to show how my increased steppage made an impact on my average.
I added a column: RollinAvgSteps = AVERAGEX(FILTER(fitbit_export_20160214, EARLIER(fitbit_export_20160214[Date])>=fitbit_export_20160214[Date]),fitbit_export_20160214[Steps])
…which takes the average of my steps to date. There are a bunch of ways to achieve this, but this is the way that I chose. And you can see that the average line is (happily) improving! Oh, and because I pulled down the extract on the 14th, there’s a dip at the end. My numbers were much healthier by the end of the day, and despite spending way too long NOT walking, I did about 7244 steps that day.
You can see the result at http://bit.ly/RobFitbit, which looks like:
Oh, and having done all this, I discovered that fellow SQL Saturday Melbourne precon presenter Reza Rad has a series on doing Power BI on Fitbit data – I was pleased to see that he did a similar set of transforms to the data.
