One challenge of working with data of a geographical nature is that sometimes, it can be mapped incorrectly. In this quick post I want to give you a couple of tips that will help you to reduce, if not eliminate, incorrect mappings of your data!
There are a few different methods you can use to try to solve the issue of incorrect mapping of geographical data.
Usehierarchiesin your map visuals, hierarchies store relationships between attributes and can help with mapping a lot. A geography hierarchy might look something like the following: Country –> State –> City – > Zip
Usedata categorization. Sometimes a state can share the name with a city or a country. I remember years ago when I heard on the news that Georgia was under attack, that was pretty concerning for me since Florida is very close to Georgia ha ha. Of course, the state of Georgia was not under attack, it was the country! We can use data categorization in Power BI to specify that a column is a city, state, zip, or country.
Remove ambiguity, for example, instead of having a city column, create a new column with the city and state. Then you can assign that new column a data categorization ofPlace. If you have millions of potential combinations then this may not be feasible within Power BI, this column would have terrible compression and most likely exceed any memory limits. However, this method works great.
Using hierarchies in Power BI to map geography data types
Take a look at the screenshot below, the State/Province of Nord in France is being incorrectly mapped to Lebanon.
Fortunately, this one can be solved very easily by using hierarchies. Nord, by itself, is not clear enough for Bing maps, however, if we add the country to the visual as well, then the picture becomes clearer and Nord will be properly mapped to France.
1) Add the Country to the Location. The country should show up above the state in the location list as seen in the following screenshot:
2) After adding the country, the map is at the highest level and you would want to now drill down to show the next level in the hierarchy. In the animated gif below, you will notice that Nord is now being mapped correctly in France!
Hope you found this tip useful! For more Power BI training, check out our On-Demand Training platform. Click below to get your free trial!
Mitchell Pearson has been with Pragmatic Works for 10 years as a Data Platform Consultant and the Training Manager. Mitchell has authored books on SQL Server, Power BI and the Power Platform. Data Platform experience includes designing and implementing enterprise level Business Intelligence solutions with the Microsoft SQL Server stack (T-SQL, SSIS, SSAS, SSRS), the Power Platform and Microsoft Azure.