Building the Power BI Strava custom data connector

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.

Leave a Reply