The Race to Alaska is a self-supported engineless boat race that runs the treacherous, isolated, and beautiful waters of British Columbia’s Inside Passage–750 miles from Port Townsend, Washington to Ketchikan, Alaska. I had the privilege of participating in the race this year as one half of Team Excellent Adventure in a 17-foot sailboat that we rigged up with oarlocks and a sliding rowing seat so we could row when the wind was light. We completed the ordeal in 16 days.

Every boat in the race was fitted with a SPOT satellite tracker that recorded speed, heading, and position every fifteen minutes. I decided to do some quick visualizations of that data to learn a bit more about our experience and the experience of the other similar and dissimilar boats that we were sailing against.

The boat selection icons are scaled to appropriately match the relative lengths of the different boats. The first visual shows straight-line distance from Victoria for each of the SPOT check-ins. Horizontal lines represent stopped boats, and the steeper the line the faster the boat was moving. Turn on MAD Dog Racing to see what a fast boat looks like. Turn on Excellent Adventure and Bunny Whaler to see a pair of boats that were pretty evenly matched–both 17-foot monohulls. After a bit of back and forth early in the race we traveled together 7/1 through 7/3 along Johnstone Strait where strong tidal currents prevent forward progress every six hours–you can see the points where we anchored and waited for the tide to change. (A)


After a pause in Telegraph Cove, Excellent Adventure continued on with only 3 stops before hitting Ketchikan (thank God for having a small bunk on the boat!). One of the reasons we stopped was strong weather (B) and you can see that it affected Bunny Whaler a bit later on. Finally you can see Bunny Whaler put the pedal to the metal and scream into Ketchikan with a killer last day. (C)

The rest of the visuals are pretty self-explanatory… I might put together a post later on explaining how I built the reports and transformed a stack of lat/lon/timestamp data into the visuals you see here. And because Power BI is pretty neat, I only need to change a single line of code to point my queries at any other race event in the database (R2AK 2015, R2AK 2017(!), Swiftsure, etc) to have this same set of interactive reports filled with the new set of data. Woo!

Let me know what you think! Big thanks to Northwest Maritime Center for putting on an awesome event! I can’t wait for next year!

Getting SharePoint-hosted Excel sheets into Power BI Desktop

A colleague presented me with an interesting quandary this week: he is building a set of Power BI reports that get their data from a stack of Excel spreadsheets which live in a folder on our internal SharePoint site. The problem is that the Folder data source only allows local paths, so it wasn’t letting him put the SharePoint folder’s URL. I had a hard time finding a solution on the web so I thought I’d share what worked for me in case there are others in the same boat.

The trick, it turns out, is to use the SharePoint.Contents  function:


This should return a table with the nested hierarchy of the documents/folders available on the SharePoint site. The complete code I used to arrive at the folder of the Excel documents navigates that hierarchy and could obviously be customized to your particular situation / derived procedurally if there is some sort of system that generates the folder names:

    Source = SharePoint.Contents("https://myhost.com/personal/myusername/"),
    Documents = Source{[Name="Documents"]}[Content],
    #"Shared with Everyone" = Documents{[Name="Shared with Everyone"]}[Content],
    myProjectDirectory = #"Shared with Everyone"{[Name="myProjectDirectory"]}[Content],
    dataDirectory = myProjectDirectory{[Name="dataDirectory"]}[Content]

The data in the Excel docs can be accessed directly at this point.

I used Windows authentication to get in and it worked great. Depending on how your SharePoint site deals with auth, that should probably work for you too.

And that’s it! Not so hard if you know which function to use. I spent a fair amount of time trying to get in with SharePoint.List (which does show up in the Get Data options but can’t get this sort of data from a SharePoint site), OData.Feed (which is what is going on under the covers but I couldn’t get it to work right), and simple Web.Contents (couldn’t even get the auth flow to work) without much luck.

Also, be aware that if you’ve got Fiddler running in the background to try to sniff out why things aren’t working, SharePoint seems to be able to detect it and instantly starts returning 404’s. So there’s that.

Anyway, post a comment if this worked for you or if it didn’t!

#shared: Your new best friend

We’ve all been there…you’re waist-deep in M code, you know there’s some function that will let you transform this list you’ve been working with, but you can’t remember what it is. You can’t use Microsoft’s online documentation because it’s out of date or you’re on an airplane or the wifi is down. What can you do to access all of the function documentation that must be hidden somewhere in Power BI Desktop?

= #shared

Enter this little snippet into the Formula Editor’s formula line, and you’ll get a list of all functions available to Power BI–even ones that don’t have online documentation yet.


Clicking the whitespace next to the Function hyperlinks will show an explanation, syntax, and often an example of how to use the function.


To make the list searchable, first convert it to a table.


Then you can use the filter column functionality to narrow the list down to the functions that you are interested in.


Being able to access a definitive list of Power BI functions, along with syntax and documentation, all from within Power BI Desktop, has been a godsend to me on numerous occasions. Hopefully you’ll find it helpful too!

Getting Started with Power BI: Object basics

As you are learning to use Power BI, it is a good idea to build a simple example data sets to see how the various functions work. The easiest way to create simple objects from scratch is to use #-functions. Here are some examples to get you started:

Elementary Types and Transformations

Perhaps it goes without saying, but Power BI interprets anything in quotes as text, numbers not in quotes as numbers, and the words true and false as Booleans.

thisIsText = "yep, sure is."
thisIsANumber = 5
thisIsABoolean = false


#date(2014,7,31) //year, month, day
#datetime(2011,12,31,16,37,48) //year, month, day, hour, minute, second
#datetimezone(2016,1,5,8,34,16,-8,0) //year, month, day, hour, minute, second, offset hour, offset minute

which will create:



#duration(6, 4, 3, 1) //day, hour, minute, second

which will create:


List: {}

A list is simply an ordered collection of objects. We use curly braces to denote lists.

someList = {1, 2, "lists can have", "different types", "inside them", true}

which will create:


Any kind of object can be in a list, including other lists, records, or even tables! You can access items in a list at a certain spot using curly braces (notice that it is 0-indexed: the first item is grabbed by asking for item 0):

fourthValue = someList{3}


Record: []

Records are collections of fields, and each field can be assigned a value. We use brackets to denote records.

someRecord=[FieldOne = "something", secondField = 26, z = {1, 2, 4, "tangerine"}]

which will create:


Field names must be text, but values can be just about anything-text, numbers, lists, records, tables, etc. You can access the value of any field in a record by using brackets:




Tables are very important because eventually, in our data model, everything is going to end up as a table. Here’s how to create a simple one from scratch:

someTable = #table({"Column1Name", "Column2Name"}, {{"Row1Col1", "Row1Col2"}, {"Row2Col1", "Row2Col2"}})

which will create:


Each row in the table is essentially a record whose fields are the column names. A table is just an ordered list of those record-rows.

In fact, Power BI treats tables exactly this way. You can grab a certain row of a table exactly the same way you would grab a certain element of a list by using curly braces (again, 0-indexed):

secondRow = someTable{1}


The result is a record with fields corresponding to column names and values corresponding to the table values.

You can also grab a certain column of a table by referencing the column name as though it is a field of a record with brackets:

firstCol = someTable[Column1Name]


The result is a list.



There are a handful of other more obscure data types as well. These, and detailed descriptions of the functions that are available for manipulating these data types, can be found at Microsoft’s documentation site.