From Text File to Map in 30 Minutes

I wanted to see where United States patent attorneys reside. Using data from the United States Patent and Trademark Office and a free account from Microsoft’s Power BI, I was able to create the two visualizations below in about 30 minutes. Pretty amazing. Let me know if you want any details on how to do this – I sketched out the process below.

Steps to Create Power BI Map Visualization

  • Grab data: text file of all active US Patent Attorneys can be found here:
  • Visit Power BI Getting Started Page so you can:
    • Download the Software (this allows you to author your visualizations); AND
    • Sign Up for a Power BI account (this allows you to publish your visualizations to the web)
  • Once you have downloaded the Desktop Power BI software, create a new report (File > New).
  • Click on Get Data > File > Text, and point to the file you downloaded (you need to extract it from the zip file if you have not already done that).
  • There are two types of maps in Power BI: maps and filled maps; I used maps that represents each node as a bubble.
  • For the top left map, just drag the City on to the Location box.
  • For the bottom left map, drag State on to the Location box, and drag State on to Size; click on the State under Size, and make sure Count is selected (not Count Distinct).
  • Finally, add a couple of tables to the right. For the top map, drag over City and Firm Name under values, and then click on the down arrow for Firm Name and select Count (this will aggregate for the city instead of showing all of the Firm Names along with their city).
    • I should have used registration number, instead of Firm Name, because this is actually a count of FIRMS by city, not a count of practitioners. Alas,  if that is the worst mistake I make today I am doing all right.
  • On the desktop, you can now click on the Publish icon in the top right.
  • Now navigate to the web version of Power BI and navigate to your app.
  • Under File select “Publish to Web”
  • Grab the “HTML you can add to your website” and paste this into a text view of your blog, and you are done. Super easy.

If you have published or stumbled upon some nice Power BI visualizations, drop them in a comment. I am a bit surprised this has not become more common. My prediction: data visualizations will become the norm in 2017, because all of these visualization tools are racing to become the standard, and are breaking down the barriers to adoption that have historically prevented people from jumping in (primarily complexity and cost). Exciting times.



Creating a Map of Your Travels with Power BI

I have been hearing a lot of great things about Power BI recently, so I decided to take it for a spin. We use Hubspot at work for our CRM, and Hubspot does not have meaningful reporting (sorry Hubspot). For example, you cannot create a report for all of your active deals in a particular location (e.g., to figure out who you should visit during a trip), because deals do not have a location – they have an associated company that has a location, but there is no way to “chain” (or denormalize) data in Hubspot.

Download Power BI

Instead of lamenting, I decided to get to work with Power BI. Simply head to this URL: and select “Download” on the left.


A minute or so later, and I was ready to roll. Now, what I did was export my Deals data and then my Companies data from Hubspot, import this into Power BI, associate the Company>Name and Deal>Associated Company, and then add these to a map. Super easy, but too much for an intro to Power BI, so let’s just create some interesting data that we can visualize with Power BI.

Enter Your Travel Data

After my download completed, I opened Power BI and clicked on the Enter Data icon on the Home tab at the top.


This allowed me to enter the countries I visited, and the years I visited those countries, into an Excel like field.


Click on the Load button and I am ready to go.

Add Data to a Map

There are 2 types of maps in Power BI: bubble maps (or point maps) and fill maps (and shape maps). Microsoft has provided an excellent primer for how to add these maps to your Power BI application. I went with a bubble map, and simply dragged the Country column from Table 1 to the Location field and ended up with a map that looks like this:


How Did I Add Smarter Labels to Power BI Maps?

For all that I love about Power BI, this seemed to be a weakness: you cannot add content to the text of the pin. For example, when I hover over Ecuador, all I see is Ecuador. I cannot add the year that I visited Ecuador in any logical way. What I did to overcome this was to drag the year into the “color saturation” column, and this adds the year to the dropped pin hover text (it also changes the color intensity, but not in a way that is noticeable). You should end up with a view like this when you hover over Ecuador:


Finally, it is always good to have a listing of your data outside of your map. I did this by clicking on the table field and then adding the Year and the Country (note: I do not think that you can chose the order of these fields, so if you chose Country as the first column you will end up with an alphabetic listing of countries, and there is nothing you can do to change this…and that is why I put year first so that Power BI defaults to order by year…which kind of makes sense here)


And that is a pretty fun map that you can go ahead and share on social media, like I did here:


How to Change Data in Power BI

Everything is easy once you know how to do it, but when I first tried to change the data that I entered into Power BI’s “Enter Data” interface, I was a bit stuck. Here is what I did: click on the elipses next to Table1 on the right side under Fields, select Edit Query, and on the right side of the modal window that pops up, I selected the gear under Source, which is under Applied steps. Seems a bit tricky, so if you have a better way, lemme know!

Elipses   Source

Initial Impression of Power BI

Overall, I am impressed with Power BI. It is powerful, and it is affordable, and Microsoft has made it very easy to experiment with. If you end up creating a map of the places you have been, let me know so I can check it out!