We have declared October to be mapping month! This month, we will focus on maps and geospatial data. There are several different maps available in Power BI as default and custom (AppSource) visuals. This week we are going to start off simple and use the Map visual to make a bubble map.
When visualizing data in a map, it’s important to understand what data is sent to external services such as Bing Maps for geocoding purposes, especially if your data is sensitive. It may be helpful to know that using latitude and longitude with the standard bubble map does not send data to Bing Maps.
Using latitude and longitude also removes ambiguity of locations and can return results more quickly. So this week, we are using Power Query to geocode locations, accepting a city as input and returning longitude and latitude as output. Then we are visualizing that data on a bubble map.
- Load the Superstore data set from data.world – only the Orders table is required
- Filter the Orders data to include only order dates from the year 2020.
- Create your own Date table in Power Query. Ensure that it contains year, month name, and date.
- Create a custom column column called Location in Power Query that combines city, state, and country
- Use Power Query to create a geocoding function. The example report uses the Bing Maps API, for which you can get a free key. Feel free to use any geocoding API that is available to you. (Click the link in this bullet point to view a tutorial on creating the Power Query function.)
- Use the geocoding function to add columns for latitude and longitude to the Orders table based upon the Location column.
- Create a DAX measure called Orders that returns a distinct count of Order IDs.
- Create a bubble map using the built-in map visual. Populate the latitude and longitude. Use sales for Size. Add the Orders measure and the Profit field as tooltips on the map.
- Create a line chart that shows sales by month name. Ensure the months are sorted chronologically.
- Add visuals of your choice to show the total sales, total profit, and total orders for the selected items in the report. The example report uses card visuals.
- Use the rectangle select to select all data points in Utah and Colorado. Note the total sales for the selection.
This challenge uses data provided on data.world that we have used in a previous exercise. You can use the data.world connector in Power BI (recommended) or download the Excel file to your local machine. You will need to log in to data.world to retrieve the data, but accounts are free.
After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.