This tutorial is a real simple mapping exercise. I was talking with a colleague today about Power BI and I was challenged to map something using latitude and longitude. I had played with mapping before but not using latitude or longitude.
I’d have to say if you want to impress someone with your PowerBI skills adding a map is a good way to do so. Typically this a functionality that you can’t add into excel, well at least not with out some serious effort.
Alright, here we go..
Resources for this project are:
- Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
- Excel file with a table in it with our location information that can be downloaded here: Locations Data Set
After downloading the Locations Data Set, Open up PowerBI and load the Excel file into Power BI. If you need to learn how to load Excel files you can follow the loading excel tutorial.
Click the Get Data on the Home ribbon. Select the first option Excel and click Connect at the bottom of the Get Data window.
Navigate to the downloaded file called Locations.xlsx and open the file by clicking Open in the bottom right hand corner.
Next, the navigator window will open. Select the table (denoted with the grid with a blue top header) called Locations. Then Click Load to load the data into the data model.
Note: there are two different icons in the Navigator window. One is called Locations which is a Table within the Excel document. While the other is called Sheet1, which is simply the first sheet in the excel workbook. For Future references it is much easier to make tables in excel and use them to load data in to PowerBI than using just a worksheet. So whenever possible try to form your data in Excel into Tables. When loading a table the headers of the table automatically load into the column names in the PowerBI data models.
We now have loaded the data into a new Table in PowerBI called Locations.
To make the map check the boxes for Latitude and Longitude. Power BI intelligently understands that latitude and Longitude are mapping functions and we are now presented with a map with tiny blue dots.
Lets add some more data to enhance the map. We can change the size of the circles at each location by dragging the column called Attenders over to the Values field for this visual.
We have now changed the size of the circles relative to each other to show the number of people that we saw at each location. To add color to the map drag the column called Event to the Legend option of the visual. This yields a map that now has each circle with a different color according to the event name.
To enhance our visual further we will add a bar chart with the total count of attenders per event. To do this click any where on the visual page (this will de-select the map visual on the page). Now click the Event column and then the Attenders column. This will present you with a table list of events and the corresponding attendees. Leaving the table visual highlighted click the Stacked Bar Chart which is in the upper left hand corner of the Visualizations window.
I circled the triple dots on the bar chart. Click the triple dots and a menu will appear. First click Sort By, then click Attenders. This will sort the attenders in descending order from the largest amount at Kohl’s Corp. down to Harley Davidson. Drag the column labeled Event to the visualization option called Legend. This colors the bar chart.
Note: The colors in the bar chart match the colors in the map we made earlier. This build uniformity in your reports and when your filtering items colors across visuals make sense.
Take some time to click on each of the bars on the bar chart. Notice how the map re-draws with only the data for that selected item. To select multiple bars on the bar chart hold the CTRL button and click on the multiple bars.
Nice job. We have finished the mapping tutorial. Share if you liked it below.
thanks for all the useful powerBI tips and tricks I was able to use in my work so far (and still learning!). For my hobby I also wish to apply powerBI. My question relates to lat / lon data however I have data in the wrong format and cannot change it easily. Question is: do I need a measure (and if so how)? or can I do some calculations right after importing the data?
The data looks like this
AA1 N52 45.557 E004 23.907
AA2 N51 34.880 E005 14.904
AA3 N51 56.904 E004 56.934
AA4 S04 34.324 W002 34.993
to bring it to the right format all text after AAX needs to be in a separate column which again can be put into 4 separate columns (that is easy in the editor). Next all N has to become + alle S – and all E + all W -; how to do that… may be by some replacing step… I have to figure that out > but my main question is: how to convert the 4 columns into that what I need: do I need to make a ‘new measure’ and how to set that up?
it comes down to the simple equation > dividing 45.557 / 60 and 23.907 / 60 etc and adding that with the first part:
this should result in
AA1 52.75928 4.39845
AA2 51.58133 5.24840
AA3 51.94840 4.94890
AA4 -4.57207 -2.58322
As far as I understand this is the format that is needed in Power BI?
Thanks for your help, kind regards, Henrike
This is a really straight forward solution. First you would want to use a function called Split Columns by Delimiter found on the Transform ribbon. After this it’s just a series of replacements and math functions. I will send you the file I created to test this out. Hope this helps. I sent you an email with the transformations to solve this.
Thank you for your dedication and hard work in putting something like this, really appreciate the whole, I’m planning to read each article to learn PowerBI.
Good luck and Godspeed!