Introduction
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.
Requirements
- 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.
Dataset
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.
Share
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.
I love these challenges and learn so much! Please keep the coming
Thanks for the comment! Glad you are enjoying them.
Hi, I tried to get the data from data.world but it seems it’s showing 400 Error to me. Any idea what I did wrong?
Hi Lavanya, the link is working for me (which doesn’t help you I realize). Perhaps try logging in to Data.World first and then navigating to Spencer’s workspace? https://data.world/jbaucke/super-store-data-model/ The file is called Sample-Superstore.xlsx
Let us know if that doesn’t work!
Thank you so much. This worked for me. 🙂