Uncategorized – ahlv.in analytics

R2AK 2017

Looks like I forgot to post this earlier!

R2AK 2019

Wow I guess it’s 2019 now! Better get the stats up to date! Near-real-time visualizations of the 2019 R2AK race!

Hoping to be out on the water next year, but stoked be cheering the teams on from the sidelines.

Give me a shout if there’s any stats or visualizations you would like to see.

The report refreshes with the latest data every 3 hours.

(I recommend clicking the arrows icon in the lower right to go to full-screen)

R2AK 2018

It’s tracker time! Near-real-time visualizations of the 2018 R2AK race!

The race only started a few hours ago and I’ve been pretty busy so didn’t get much of a chance to draw up anything super sophisticated. Plus the tracker’s REST API is all changed (for the better!). Check out what I’ve got so far:

(I recommend clicking the arrows icon in the lower right to go to full-screen)

Seventy48 2018

Hey friends I’m at it again! Pilfering data from the excellent folks at Swiftsure to build some near-real-time visualizations of the first-ever Seventy48 race!

(I recommend clicking the arrows icon in the lower right to go to full-screen)

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.

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

Duration

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

someRecord[secondField]

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:

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.