With Microsoft Power BI’s amazing visualization capabilities, you might assume that creating fascinating visuals is just a matter of ‘drag and drop’. This is true for simple data sets but when your data contains details such as the locations, creating visuals becomes more than just a matter of ‘drag and drop’.
The Good News … Get your Good News Here!
With just a few steps in the right direction, you can create map visuals with the ‘drag and drop’ method.
This blog highlights the common mistakes that all beginners do and guides you how to avoid the pitfalls.
Spatial data must be pre-processed to provide accurate mapping. Let’s first have a look at the dateset required to build maps. Maps are supported in Power BI for a data set containing spatial features. These features include any information related to the location such as longitude, latitude, zip, City, State, etc. The data set also contains records for a value measured (for example Units Sold) at the particular locations.
The data set used for this exercise contains information on sales figures across 11 states in the United States of America.
The Common Mistake!
Before starting the exercise, Drag and Drop the City from Spatial Data to the Fields pane in the right side of the window.
Power BI will automatically generate a Map Visual. The visual will show some points outside the USA in Europe, Australia, South America, Asia, Africa and Canada, which we will learn how to map accurately. This visual is shown below.
Data Transformation needed for accurate mapping
To ensure accurate mapping of points, data must be transformed to provide a complete address for each location.
To transform data:
1.From the Ribbon Menu, click on Home >Edit Queries >Edit Queries.
Microsoft Power BI Query Editor opens.
2.Click on Zip. Click on Transform > Data Type >
A pop-up menu appears. Click on Replace Current.
3.From the Ribbon Menu, click on Add C
4.From the General Section, click on Custom Column.
A pop-up menu appears.
5.In the New Column Name, type “Location”.
6.Now select City from the available columns section.
City field appears in the custom column formula section.
7.Type the following formula in the custom column formula section.
8.Click OK.
A new column containing complete address of the location points is created.
9.Click on Home Tab in the Ribbon Menu.
10.Click on Close & Apply.
11.Now Remove the City Field from Location value in the visualization section.
12.Drag the newly created Location column to the Location
All the locations have been correctly mapped to United States.
Setting appropriate Data Types for Spatial Data
Power BI does not recognize spatial data types by default. Therefore, data Types must be adjusted before we formally begin to visualize our data.
To set Data Types:
1. Click on Longitude in the Fields Pane.
2.Click on Modeling in the ribbon.
3.From the Properties section, click on Data Category.
The default value for data category is uncategorized.
4.Click on Longitude from the drop-down.
Click on Default Summarization > select Don’t Summarize
6.Click on Latitude in the Fields pane.
7.Click on Modeling > Data Category > Latitude from the drop-down.
8.Click on Default Summarization > Don’t Summarize.
9.Click on Zip in the Fields Pane.
10.Click on Modeling > Data Category > Postal Code.
11.Click on Default Summarization > Don’t Summarize.
Now that you have preprocessed the spatial data, you are ready to create maps in Power.
Conclusion:
Spatial data contains information related to the locations. This is known as geo-coding. Microsoft Power BI works on the principle of string matching therefore, your spatial data must be categorized and transformed into a way which is more recognizable for the Power BI’s engine. In this blog post, we revealed the common mistakes that lead to inaccurate mapping and suggest simple methods to avoid them.
If you liked our content, drop your email in the comment box below and receives regular updates related to our blog posts!
Before you go !
Are you a data analyst and want to learn more about Power BI?
Why not sign up for Power BI training in Australia. We provide our services in the following regions:
- Power BI Training in Sydney
- Power BI Training in Melbourne
- Power BI Training in Brisbane
- Power BI Training in Canberra
