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.