R2AK

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)

R2AKanalysis

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!

#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.

#shared001.png

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

#shared002.png

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

#shared003

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

#shared004.png

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/DateTime/DateTimeZone

#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:

ObjectBasics003

Duration

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

which will create:

ObjectBasics004

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:

ObjectBasics002

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}

ObjectBasics006.png

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:

ObjectBasics005

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:

someRecord[secondField]

ObjectBasics007

Table

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:

ObjectBasics001

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}

ObjectBasics008

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]

ObjectBasics009

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.