Resolving Unexpected Behaviors with Latitudes and Longitudes in Power View and the Power BI Desktop

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:

Comparison

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:

Category and Data Type 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.

Chart Spec

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:

Count of Latitude

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:

PBID Latitude as Text and Categorized

Apparently the category is treated as secondary to the data type.  Therefore, one must type latitude and longitude fields as Decimal Number.

0 comments:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>