This is the second Power BI data connector I have created, the first was for the Office of National Statistics.
As a keen runner I like the idea of viewing my running data on a dashboard and having it reload automatically.
Lucky for me a few people have already been down this road so I could borrow their code (see Kasperonbi link here). I suggest you read Kasper’s post if you want the background detail, I’m not going to repeat it here.
Unfortunately Strava changed their authenticaiton method, meaning the code would only allow you to be authenticated a six hours. (Not much good if you want to schedule this reload on the Power BI Portal.)
I have amended the code to use a refresh token (see this link for more background reading). Including a new refresh funciton (see below).
TokenMethod = (code) =>
let
Response = Web.Contents("https://www.strava.com/oauth/token", [ // Step 2 - Token Exchange (Get access token)
Content = Text.ToBinary(Uri.BuildQueryString([
client_id = client_id,
client_secret = client_secret,
code = code,
//redirect_uri = redirect_uri // Removed as not required
grant_type = "authorization_code" // Added this as missing
])),
Headers=[#"Content-type" = "application/x-www-form-urlencoded",#"Accept" = "application/json"]]),
Parts = Json.Document(Response)
in
Parts;
Refresh = (code,refresh_token) =>
let
Response = Web.Contents("https://www.strava.com/oauth/token", [
Content = Text.ToBinary(Uri.BuildQueryString([
client_id = client_id,
client_secret = client_secret,
code = code,
grant_type = "refresh_token",
refresh_token = refresh_token])),
Headers=[#"Content-type" = "application/x-www-form-urlencoded",#"Accept" = "application/json"]]),
Parts = Json.Document(Response)
in
Parts;
*All the data connector code is available on my github: https://github.com/mrsheraton/PowerBIStravaConnector
The resulting dashboard can be seen on my website: https://www.blueoceanweb.co.uk/strava-powerbi-dataconnector/
*Bear iin mind the Strava API limits users to 600 calls every 15 minutes.
Displaying Maps with Strava Data
I also wanted to display my route maps in Power BI, which I achieved, see below:
To achieve this I converted Polylines to longatitude and Lattitude pairs, using the following Python script:
# 'dataset' holds the input data for this script
import pandas as pd
def decode_polyline(polyline_str):
index, lat, lng = 0, 0, 0
# list
coordinates = []
# Set
changes = {'latitude': 0, 'longitude': 0}
while index < len(polyline_str):
# Gather lat/lon changes, store them in a dictionary to apply them later
for unit in ['latitude', 'longitude']:
shift, result = 0, 0
while True:
byte = ord(polyline_str[index]) - 63
index += 1
result |= (byte & 0x1f) << shift
shift += 5
if not byte >= 0x20:
break
if (result & 1):
changes[unit] = ~(result >> 1)
else:
changes[unit] = (result >> 1)
lat += changes['latitude']
lng += changes['longitude']
coordinates.append((lat / 100000.0, lng / 100000.0))
return coordinates
dataset['latlong'] = dataset['Polyline'].apply(decode_polyline)
dataset= dataset.set_index(['id', 'Polyline']).explode('latlong').reset_index()
dataset['latitude'], dataset['longitude'] = zip(*dataset['latlong'])
print(dataset)
Then loaded the longatiude and lattitude pairs into the the route map visualuation.
I’ve not gone into a massive amount of detail, but if you have any quesitons or commnts please leave them below.
I’ve been able to load my Strava data into PowerBI using your code.
I’m now trying to add the map visual, but on trying to convert the polygon using the Python script,
I get an TypeError: object of type ‘float’ has no len()
It seems Polyline is loaded as float i.s.o. string.
What conversions did you apply on the Polyline colum ?
.
For some activities the polyline contains null
which causes the apply to fail.
I’ve filtered out the rows with polyline null, and the script works
Glad you got it working. It’s been a while since I looked at this.
Is a sample .pbix file available for your report? I’m struggling to get the python script to run and would love to see your table structure. thanks
I’ll send you the .pbix file to your hotmail address. I’ve not really looked at this for a while but hope it’s useful.
Hi – Do you have any more advice on setting up the map? I have got my dashboard working pretty well, but don’t quite get the python part. I am also having issues loading in more than just this years detailed data. Is there a day to slowly import old data without hitting the API limits? (I get a retry failed multiple times quite alot)
Maybe it’s best to work backwards.
You’ll need to get the free PBi custom visual Route Map by Weiwei Cui.
This requires 3 inputs. Timestamp, Latitude and Longitude.
When you perform an activity there could be hundreds of Latitude and Longitude values tracked. A polyline is an encoded string that efficiently represents an entire activity route with a single value. You need to transform that single value back into the many Latitude and Longitude values. That’s where the Python script comes in.
The PolyLine values are held in the DetailedActivity table.
This is based on a strava DetailedActvity, each activity has a value called Map which is an instance of a PolyLine.
https://developers.strava.com/docs/reference/#api-models-DetailedActivity
https://developers.strava.com/docs/reference/#api-models-PolylineMap
The best way to test this is create a table with two columns, id and Polyline.
Create one row, where id=1 and PolyLine=’ivq~FvoyuOi{l~FjzdvO’
As the next step click Transform > Run Python Script and copy and paste the code in from my post. This should produce a table, expand that and make sure to set Latitude and Longitude fields to decimal types. You’ll also need to add a sequential index.
Now going back to the three inputs for the Route map.
Set them to Timestamp=Index, Latitude = average(Latitude) and Longitude = average(Longitude).
Did you see my article on handling the api rate limit ?
https://www.blueoceanweb.co.uk/api-rate-handling-for-strava-power-query-custom-data-connector/
What error are you getting ? How many activities did you perform in the last year? What is your API rate limit ? (they are not standard).
Thank you very much for this, I have the connector working, but it’s taking a very long time to load the data in PBI, is this normal?
Glad to hear you got it working. Yes it’s slow, I’m not really sure how much of that is PBI vs the Strava API. I have about 1800 activities and it takes a couple of hours each day (when It doesn’t give a random server error, which I’ve been getting a lot lately). PBI probably isn’t the best tool to do this sort of thing to be fair, as really I’d just want to load the new activities and append to the old but doesn’t seem possible in PBI.