A couple of months back I wanted to do a simple map showing the location of health centers in Puerto Rico based on latitudes and longitudes, using Power View and PowerPivot. However, Power View insisted that all of the locations where off the west coast of Africa! After battling back and forth for a while and triple-checking the values directly in Bing Maps and in Google Maps, I discovered that the data types of the latitude and longitude fields had to be Decimal Number. Look at the before and after results:
I would have quickly discovered the need for the Decimal Number data type if I had tried to categorize the fields as Latitude and Longitude (respectively) in PowerPivot, as it gives this warning:
The categorization didn’t initiallly occur to me as I was telling the map chart exactly what fields to use for Latitude and Longitude; I thought that was enough indication.
The Situation in the Power BI Desktop
In the Power BI Desktop the data type expectation is immediately obvious because if the fields are text, it will force a COUNT aggregation:
This can be noticed as soon as the field is dragged to the box, but we cannot get rid of the COUNT because it lacks the familiar “Do no summarize” option that we have in Power View. Moreover, categorizing the field (as shown below) does not resolve the issue either:
Apparently the category is treated as secondary to the data type. Therefore, one must type latitude and longitude fields as Decimal Number.
