Building the Power BI Strava custom data connector – (Updated)

Since my previous post, about the Power BI data connector a few things have changed, which I felt warranted a new post.  This post is purely to show what I did and hopefully other people find it useful.

First there is a new Visual Studio Code Power Query SDK, which only works in Visual Studio Code.  (The previous connector was coded in Visual Studio 2019).

Second whenever I refreshed the dashboard it was giving the error: 429 Too Many Requests.  (This error is produced by Strava when there are more than 600 API calls in 15 minutes.)

Objectives

  1. Convert the project from VS2019 to Visual Studio Code and use the new SDK
  2. Debug and fix the problem related to the Too Many Request error
  3. Add the ability for the user to optionally use date range to filter the following datasets: Activities (Comments), Activities (Detailed) – Laps –  Segment Efforts.  (This will reduce the number of API calls and help avoid the 600 API limit)
  4. Refactor code where needed and add new datasets

Convert project to Visual Studio Code 

I downloaded & installed Visual Studio Code.  (Do not make the mistake I made and install this on a Macbook using Silicon or it will not work.  I ended up renting a virtual machine on Azure running Windows.)

Installed the latest Power Query SDK.  You can do this from within Visual Studio Code. 

Opened the existing project In VSC (File  > Open Folder) making sure to select the root folder.

The Power Query files are detected and I was promoted to enable the PQ SDK.  (If you do not get this message make sure you’ve selected the folder holding the .pq files and not a parent folder).

In the bottom left coner in it show shows Power Query SDK, once expanded it shows the options below.  The project has now been successfully converted to Visual Studio Code.

 

Too Many Request error – Debug and fix

 To enable debugging go to the debug view and click create a launch.json file

The additionalArgs value is required for debugging and is not always added automatically:

{
    // Use IntelliSense to learn about possible attributes.
    // Hover to view descriptions of existing attributes.
    // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
    "version": "0.2.0",
    "configurations": [
               
        {
            "type": "powerquery",
            "request": "launch",
            "name": "Evaluate power query file.",
            "program": "${workspaceFolder}/${command:AskForPowerQueryFileName}",
            "additionalArgs": ["--logMashupEngineTraces", "all"]
        }
    ]
}            

The project now has debugging enabled.  See  TripPin 8 – Adding Diagnostics – Power Query | Microsoft Learn for more information.

 The original code was passing now depreciated paging paranmeter.    The Strava List Activity Comments end point has two parameters related to paging and they have been depreciated and replaced, page to after_cursor and per_page to page_size.

This was causing the code to request different pages but because the page parameter was invalid the first page was always brought back:

activities/8777569632/comments?page=1&per_page=100
activities/8777569632/comments?page=2&per_page=100
activities/8777569632/comments?page=3&per_page=100
etc

Eventally causing the API limit to be reached. 

After modifying the code to use after_cursor it worked fine.  You’ll need to look in my code to see what I actually changed (hint look in StravaData.Page & StravaData.PagedReader)

Additional datasets & user date filter

The Navigation Table in Power BI now shows the following datasets:

  • Activities (Summary)
  • Activities (Comments) *
  • Activities (Detailed) *
  • Activities (Detailed) – (Laps) *
  • Activities (Detailed) – (Segment Efforts) *
  • Activities (Detailed) – (Best Efforts) *
  • Athlete
  • Clubs

* All the tables are affected by the new date range filter.

Only one API call is required for the List Athlete Activities (excluding paging).  However Detailed Activity and List Actvity Comments require a call per Activity.  This means you’re much more likely to hit the 600 API call limit if you include more detail.  To get around this I’ve added a date range, with start date mandary and end date optional.

The modified data connector code is available on my github https://github.com/mrsheraton/PowerBIStravaConnector

 

See an example Power BI dashboard using this data here www.blueoceanweb.co.uk/strava-powerbi-dataconnector/ 

Below are a list of all the Strava End Points this code is using.

Activities

Athlete

Clubs

Leave a Reply