The Digital Rolodex

A few months ago, I was doing my civic duty as part of a potential jury panel. While we were waiting for the court to select the jurors, the bailiff took us into an empty courtroom across the hall. As part of his job to keep us engaged (and distracted) until most of us could leave for the day, he showed us how he winds up the huge old grandfather clock prominently standing against the wall each morning, so it displays the proper time. This is especially important in a serious courtroom where there's an expectation to tuck away phones from the general view.

This also got me thinking about all the old technology that's still usable (like this huge clock), but it's not necessarily current for today's world. Examples of this include the phonebook, dial-up internet, and phone booths. And this also includes Rolodexes (see this newsletter's cover image), which at one point served as a physical contact book for businesses and individuals long before we stored those details on our phones. It's also an analogy for organizing items in an order that enables us to easily access an item when we need it, much like index functions that we find in DAX, Excel, and other programming languages.

Rolodex Notes: I couldn't share this newsletter without including a bit of history on the Rolodex (a portmanteau of the words Rolling and Index)! The Danish engineer Hildaur Neilsen invented it in 1956 while at the stationary manufacturer Zephyr American in New York. It publicly debuted in 1958 and Rolodex cards are still for sale today (in 2023)!

DAX Index

One way we can create a lookup function in Excel is through a combination of the MATCH and INDEX functions. The INDEX DAX function is also now available in Power BI! Here's how to use it to access a value by its position in a list, where the list we reference can come from a column in an existing data table.

Other Index Options

Interestingly, DAX uses an index of one to match the same index start value that Excel also uses (they share many of the same functions). But most languages use an index of zero. Here's how they compare (notice that R and SQL - the other prominent data science languages - start at one). To clarify, I don't use a lot of these languages, but I included them in the graphic as reference points! And yes, APL is an oddball language indeed where the index starts at either zero or one.

Python vs. Excel

There's enough conflict in the world right now without creating additional conflict by trying to definitely say that one tool or language is better than another. Whether I choose to use Python or Excel so often depends on the task at hand (data volume and deadlines to name a few constraints). But Excel introduced a number of really interesting array functions in the past year or so. I found that comparing these functions to those for NumPy (Num-Pie) arrays from the Python language was a helpful point for me personally to start learning more. Python is an elegant and fun language to work with, and these characteristics extend into functions from packages/libraries/modules like NumPy. I actually think the brackets in these NumPy arrays make them really easy to see and subsequently visualize how to use them in other functions. Here's how these functions work on a two-dimensional array.

The graphic above shows how to create the array, and then determine the number of rows and columns within it (its shape). However, creating the arrays (in either Excel or Python) is very rarely useful by itself. Instead, we want to leverage them in additional functions. This includes transformations like stacking them together or transposing them.

NumPy Arrays

Within Python specifically, NumPy arrays are an incredibly helpful way to speed up data processing on large pandas DataFrame or Series objects. We can also use them to access a single value in an array by its position (or several values by a range of access positions). The NumPy library is also incredibly helpful for linear algebra (but that's a topic for another time)!

To put Python into action, here's an example script using the NumPy library (and others) to run within Power Query. It allows us to connect to the data, transform it into a usable (and useful) data table, and finally run an algorithm (like linear regression) on it before loading all the data into Power BI. It's an earlier video from my Power BI Weekly series, but it's still one of my favorites!

Coming Up!

In addition to the weekly debut of a new topic in the Power BI Weekly series, I have a new course update that I'll share more about soon!

-HW