Why does my location show incorrectly in Power BI Maps?

Nov 18, 2021 | Power BI Hints and Tips

If you are dealing with location related data in Microsoft Power BI Desktop then using maps can be a really helpful way to show your information. 

For example, it can help you see where your clients are, or maybe more importantly, are not.  This could trigger a marketing campaign in the areas that are not covered increasing the customer base. 

Normally these places plot perfectly in Power BI but what if the data is ambiguous?  For example, New York could be a city or a state, Newcastle could be in the North East as Newcastle upon Tyne or in the Midlands as Newcastle under Lyme.  This is where problems can occur and knowing how to resolve them is vital. Power BI uses an algorithm to determine where data is but it really is just a best guess! 

However, there are 2 solution that can solve the problem.  The data category can be set to specify what type of location a field contains.  Alternatively, 2 columns of location data can be concatenated to make the place more specific. 

Data Category 

This can resolve as issue where the location is viewed in the wrong category such as New York being viewed as a city when in this case it is a State or vice versa. 

  1. Select Data on the left of the Power BI Desktop screen 
  2. Click in the field to be categorised. 
  3. Select the Column Tools tab on the ribbon 
  4. Select the correct category from the following:- Address, Place, City, County, State or Province, Post Code, Country, Continent, Latitude or Longitude. 
Data Category

5. The field will show in the field list with a globe symbol to denote it is categorised as a location 

More Column Options in Word

NOTE:  A combination of categories such as a city and country would be categorised as a place.  If it is categorised as a city it will not display on the map. 

Concatenate location fields 

This solves the issue where a location is ambiguous and could be in 2 different countries or 2 different counties.  By combining fields together the location becomes more specific. 

  1. Select Data on the left of the Power BI Desktop screen 
  2. Select the Column Tools tab on the ribbon 
  3. Click on New Column.  This now shows a formula bar where the calculation can be displayed 
  4. The ‘&’ sign is used to concatenate information together 
  5. For example: City and country = Customers[City]&”, “&Customers[Country/Region] 
Insert Table Options in Word

6. This will produce data such as Leeds, UK 

7. Even if a category is also assigned to this data the symbol beside the new field will denote it is a calculation. 

Calculation symbol

NOTE: When entering the calculation if you start entering the field name then a list of options of fields and functions appears.  The correct one can be clicked on to add it to the calculation.  The fields will show the table name followed by the field in square brackets.  E.g. Customers[City] is the customers table and the city field from that table. 

NOTE: When concatenating fields enter them is hierarchical order with the most localised location first. 

Further Reading

If you’ve enjoyed reading about to use maps in Microsoft Power BI, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Power BI? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Power BI Courses I run.