Aug 20, 2018

Power BI: Building a Digital Nomad Dashboard

The term 'Digital Nomad' has seen a rise in popularity in recent years. Although, according to Google trends, it's not as popular as it was in 2004.

At any rate, the number of people working remotely as increased quite a lot, and is becoming a standard benefit in the workplace. According to Global Workplace Analytics, "The regular work-at-home population has grown 140% since 2005" (http://globalworkplaceanalytics.com/telecommuting-statistics).

If you take remote working to the next level, you enter the Digital Nomad status (queue dramatic music). These workers not only telecommute, but also travel the world at the same time (for extended periods of time). My wife and I took two trips as digital nomads, each for about 4 months. Our first trip was in Europe, and the second was in South America. Both were amazing journeys and I would recommend everyone to try it at least once! Not many people can say they've experienced a lively orchestra in Barcelona, drank delicious wine with the locals in Athens, or almost passed out in the Nazca dessert (all worth it!).

It turns out one of the most difficult things about becoming a digital nomad, is planning for it. Quoting Benjamin Franklin: “If you fail to plan, you are planning to fail!” There are so many factors to consider when deciding on a country to spend your life in. Safety, accessibility, infrastructure, political stability, language, food... the list goes on... There are many websites to assist in the decision making process. One of the most helpful for us was nomadlist.com. They collect many helpful stats via crowdsourcing and make it easily accessible to anyone. It contains data about most countries and cities that you would want to visit. The dashboard that I created for this entry uses this website to pull the data.

Please check the website out. You'll find quite a lot of interesting information about the world: https://nomadlist.com/

Using Power BI to simplify your decisions

Fortunately for us, Power BI is a software designed to help make complex decisions. There are four steps I took to create this report.

Make a list of Cities.
Connect a query to the web.
Create a function to connect to the web.
Loop the function over the list.
Build the reports.

1. Make a List of Cities

The practical idea here is to come up with a list of potential cities you would want to go to. Maybe a max of 20, but for my list, I only entered 9. The list of cities can be entered into an Excel spreadsheet or directly into Power BI. For my list, I just created a table in Power BI with the "Enter Data" feature.

If you were curious what's actually happening when you create a table by entering data.... Power BI is actually storing the values you enter in binary form on the M script for Power Query read. Here's what it looks like (I removed the binary code to save space).

let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "BINARY CODE HERE", BinaryEncoding.Base64), Compression.Deflate) ), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), CitiesTable = Table.RenameColumns(Source,{{"Column1", "Cities"}}) in CitiesTable

The cool thing here is that you can actually copy the query from Power BI, and use it in Excel's Power Query, even though Excel doesn't have the "Enter Data" feature.

2. Connect a Query to the Web

Once you have a list you are satisfied with, we need to use Power Query to connect to our web address, nomadlist.com. We can do this by navigating to Get Data -> Web. But before we go on, it will be helpful to know how the URL looks for each city. Ultimately, we want to create a function to access a specific webpage for a specific city, not just the home page.

If you navigate to to any country on Nomad List, you'll notice that the name of the city is just tagged onto the end of the main pages' URL. For example, Tokyo is just nomadlist.com/tokyo.

Keep the upper/lowercase in mind for your list of cities: nomadlist.com/Tokyo will not work here.

So if your list has uppercase, you'll need to change them to lowercase as we will be using the list to feed into our function that uses the URL. To begin creating the function, we'll start out with a query which we'll later on convert into a function. Pull up the Get Data -> Web and insert a URL for any city:

Click 'OK' and Power BI will connect to the web address you provided. Sometimes working with websites can be a bit tricky, but working Nomad List is quite simple since the data is stored in HTML tables. This makes it very easy to pull in the data since we just need to select which ones we want. For my dashboard, I chose tables 0, 1 and 3.

If you chose those same tables, you should now have 4 queries in your query editor (3 web tables and 1 table for your cities). From here you can format and split the data as you like. But you should perform a pivot on each of the queries as your last step.

So the table goes from this:

To this:

If you end up with whole number values instead of the expected values above, the pivot transform inserted an extra step to aggregate (cout) the values instead of no aggregation. All you need to do is remove the ", List.Count" at the end of the formula:

= Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Category]), "Category", "Value", List.Count)

To...

= Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Category]), "Category", "Value")

Be sure to pivot each of your queries so that they end up being one one row (and one header) with several columns instead of 2 columns with several rows.

3. Create a Function to Connect to the Web Address

We don't want to do that process for each entry in our list of cities. That would just be a complete waste of time! Instead, we are going to turn those queries into functions that we can feed our list of cities to.

To turn our query into a function, we just need to add:

(yourVariable)=>

at the very top of our M script, and change the URL city text with the variable. So instead of this:

let Source = Web.Page(Web.Contents("https://nomadlist.com/tokyo")),

...

We have this:

(city) =>

let Source = Web.Page(Web.Contents("https://nomadlist.com/" & city)),

...

*Note - we could also specify the data types to ensure the function runs properly with "(city as text) as table =>". Specifying the data types is generally a good idea to ensure good performance.

Open the advanced editor and make those changes to each of your queries. Click 'Done' and you should see the Power Query editor replace your table with an input window. Here you can enter any city of your choosing, and Power Query will run it through the steps to get the data - pretty cool!

You should test out your functions this way. If you run into any errors, double check that the spelling of the city name is the same as the URL address. Buenos Aires would be buenos-aires with a hyphen.

If everything works, you are now ready to loop this function over your city list!

4. Loop the function over the list

To keep everything in order, I like to create a reference to any main table objects (the list of cities in this case). This way, I can always refer back to the original query, and possibly make another query off of it. To quickly create a query based off of another, you can right click any query, and select 'Reference'. Power Query will create a new query for you. And you can see that the first step in this new query is referencing the original query as its' source.

Within this new query (and you should name it an appropriate name), we are going to 'Invoke a Custom Function' on it. This will create a new column for the selected query. The column in our case, will hold a table of our values we pulled from the web.

Once you click 'OK', it'll take some time to finish because it's scraping the web for each of the cities in your list. Once finished, your table will now look like this:

Because our function is returning a table, we get a column with hyperlinks titled "Table". If we wanted just a single city's information, we could click on the Table hyperlink and it would give us only that information. Since we want data for ALL cities, we need to use the double arrow button next to the "Main_Info" column. Click on that button and a drop-down list will appear.

My personal preference is to uncheck the "Use original column name as prefix" option. If you know some column names will be the same, I would leave this checked though. Having this option checked will result in the new column names like "Main_Info.Cost" as opposed to just "Cost".

After click 'OK' you should see some familiar numbers for each of your cities!

Great job! You've now successfully created a function to scrape data from the web!

You can do this same process for each of your query tables (0, 1 & 3). Once finished, you can load the data into the Power BI model to create some nice visuals!

5. Build the reports.

This part is up to you, since you are the only one who knows what's important for your digital nomad journey. If you have a lot of cities, you can really start to pick out some of the correlations between costs, WiFi speed, and overall fun. In my list, Barcelona has a lower cost of living but the fastest WiFi speeds: