“Power BI” permit to use in easy way Geographical information to give an efficiente and quickly Visualization of your data. Not many knows that to simplify your and “Power Bi” work you have to prepare correctly your data.
In this article i try to explain better how to prepare your data to use it in power bi!
Start from data
We start with a common Dataset:
We have imported a dataset of City of Italy. We have some information about Country, Region and County. So we want to use this info in two different visuals:
- Filled Map
If we insert attribute city in map this is the result:
Wow i have only Italian Cities but my map show that i have some Italian Cities on US and India. I think this it’s not working.
If i use Italian province in filled Map i have this result:
As you can see it’s not working But why? we have two different problem.
Start with normal map
The problem is that there isn’t a reference to Country. In fact Map use Bing to search your City, if there are more cities it shows all the cities with the same name. you want to resolve this problem you can do 2 diffent things
- Use latitude and longitude,
if you have this information in your dataset it’s very simple to resolve your problem. In the map visual add latitude and longitude attribute in the corresponding tab
- Create a custom Column:
if you have Other information about your city for example Country you can create a custom column with this information combined. Start from the smallet ( in our case the city) and grow up to the biggest (in our case the country) insert a space between attribute. To complete the operation you have to categorize your attribute. to do that you have to Select your column go to modelling tab and under Data Category select City:
If we return on our map this is the result:
With the filled map the problem is different, in this case Power BI can’t recognize what kind of attribute you use to create a filled Map. To teach PowerBI that My province column on my dataset is a “Province” i have to do the same thing done before.
Select your column on data go to modelling tab and under Data Category select Provice. Now power bi know that this column it’s a provice and can give this information to bing, the result it is:
As you can see it works correctly. Remember if you have a geographic attribute in your dataset we have to define the category of this attribute to teach “Power Bi” the kind of data and how to use it in a map.