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: https://powerbi.microsoft.com/en-us/get-started/ 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:
Using #PowerBI to visualize the countries I have been lucky enough to visit. Very impressed by the ease of use. pic.twitter.com/deWeqMStaY
— Matt Dyor (@mattdyor) June 19, 2016
//platform.twitter.com/widgets.js
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!
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!
Thanks.
Matt
