One of the most powerful features in Power BI and Excel is supporting geospatial visualisations. In Excel we can use Map visualisation in Power View, or use Power Map directly. In Power BI, as you know, there are two built-in visualisations supporting geographic coordinate data, Map and Filled map. They work beautifully if you have enough data supported by Bing Maps. But, there are some issues with Map visualisations in both Power BI and Excel. In this post I address some of the issues I faced myself and I’ll provide the solutions for the issues. As “Filled Map” and “Map” visualisations in Power BI are very similar my focus in this post would be on “Map” visualisation. My intention is not explaining Power View and Power Map that much so my focus in this article would be on Power BI more than the other two.

Requirements

To experiment everything I explain in this post you need to have:

• The new SQL Server sample, WideWorldImportersDW (WWI). You can download it here
• The latest version on Power BI Desktop (current version is 2.35.4399.381 64-bit (May 2016))
• Excel 2016 or Excel 2013

If you use Excel 2016, then you need to turn on Power View on.

Check this out if you want to learn more about BI features in Excel 2016.

Get Data in Power BI

• Open Power BI Desktop
• Get Data from SQL Server Database
• Select Fact.Sales and Dimension.City then load data

Map Issues In Power BI

Wrong Cities in Power BI

• Expand the “Dimension City” table
• Select “City” column then change its Data Category to City (Data Category is on “Modeling” tab from the ribbon)

• Put a Map visual into the page
• Put “City” on Location
• Put “Total Excluding Tax” on Size

As you see sales distributed across different countries, but, this is not quiet right.

• Put a slicer on the page then put “Country” on the slicer
• Click “United States” to filter the Map

Oops! This is not quiet right. What happened is that Bing Map Engine gets confused with the city names so that it shows a city with the same name outside of the US, just like New Plymouth which a city in New Zealand, but, the New Plymouth we have in our data source is the New Plymouth from Idaho in the US.

Fixing the Wrong Cities Issue In Power BI

Solution 1: Concatenating Country and City in Power BI

We can easily overcome the issue by adding a calculated column to the table and concatenate “Country” and “City” columns so that the Bing Map engine finds the correct cities. We can do this is Power Query or writing DAX expressions. I explain DAX solution and leave the Power Query scenario to you.

• Right click the “Dimension City” table then click New Column

• Type in the following DAX expression

Country, City = ‘Dimension City'[Country] & “, “ & ‘Dimension City'[City]

• Click the “Country, City” column then from “Modeling” tab change its Data Category to “City”
• Click on the Map
• Replace “City” with “Country, City” on Location

Hmm! It looks much better now. Switch to “Data” view to see how the new column looks like.

Solution 2: Preparing Location in Power BI

We can use “Location” column on the Map to get more precise points. Looking at the “Location” column we quickly see that it contains latitude and longitude information which is exactly what we need. We only need to remove “POINTS( “ and “)” from the string and then replacing space with comma to get location data based on latitude and longitude from the “Location” column.

• Click “Edit Queries” from “Home” tab from the ribbon

• Open “Dimension City”
• Right click “Location” column and click “Replace Values”

• Type “POINT (“ in “Value To Find” and leave “Replace With” empty. This removes the “POINT (“ from the text

• Right click “Location” and click “Replace Value” again
• This time replace “)” with empty

• At the end replace space with comma

• This is what we get

• Now click “Close and Apply”

• Click the Map then replace the “USA Cities” with “Location” column

Oops! What is wrong now? The location meant to show more precise points on the Map, but, why it shows most of places in Antarctica? Seriously?

Here is when the next issue shows up.

OK, keep reading to find out why Map shows wrong data.

Power Query Issue in Converting SQL Server “Geography” Data Type to Power Query Text

Let’s have a look at the whole process that we went through so far to find out what the issue is. We got data from the Dimension.City table from SQL Server, right? Click “Edit Queries” and have a look at the “Location” column again.

Click “Navigation” step from “Query Settings” pane to see what we had originally in “Location” column before doing any transformations.

Let’s have a look at the Dimension.City as well and see what we really have in the source table.

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance that hosts the WideWorldImportersDW database
• Query Dimension.City

The “Location” column data type in SQL Server is “geography” which means we can easily get latitude and longitude from the location column in SSMS. Now run the following query to get latitude and longitude from the Location column:

SELECT [City Key],

       [WWI City ID],

       City,

       [Location],

       [Location].Lat AS Latitude,

       [Location].Long AS Longitude

FROM   Dimension.City

Note: Geography is a .NET Common Language Expression (CLR) data type available in SQL Server. In the above query “.Lat” and “.Long” are properties of geography instance. The properties of geography are case sensitive, so if you type “[Location].lat” rather than “[Location].Lat” you’ll get the below error message:

“Could not find property or field ‘lat’ for type ‘Microsoft.SqlServer.Types.SqlGeography’ in assembly ‘Microsoft.SqlServer.Types’.”

To learn more about geography data type in SQL Server refer to the below links:

• SQL Server geography data type
• .Lat property
• .Long property

OK, let’s have a look at both SQL query and Power Query result side-by-side.

Well, we uncovered the issue now. For some reason, Power Query, erroneously converted SQL Server geography data type to text. The conversion pattern should be “POINT (Latitude Longitude)” rather than “POINT (Longitude Latitude)”. It looks like a bug. I already reported this as a bug to Power BI team. If you also want to report it click here.

But, what we should do until Power BI team fixes the issue? Well, we can overcome this in two ways.

• We can split “Location” column in Power Query to two columns, Latitude and Longitude
• We can use the SQL query we wrote previously and import the “Dimension.City” table again

Solution: Split “Location” column to Latitude and Longitude

We already went more than half of the way in the previous section when we transformed “location” column from “POINT (-78.651695 42.1083969)” to “-78.651695,42.1083969”. So we literally need to split the “Location” column to Latitude and Longitude.

• Click “Edit Queries”
• Browse “Dimension City” from Queried pane
• Right click “Location” then“Split Column” and “By Delimiter”

• Select “Comma” as delimiter then click OK

• Rename “Location.1” to “Longitude” and “Location.2” to “Latitude”

• Click Close and Apply
• As we split “Location” column to two new columns, the Map breaks, click “Fix This”

• Change data category for both “Latitude” and “Longitude” columns
• Set “Default Summarization” property of both “Latitude” and “Longitude” columns to “Do Not Summarise”
• Click on Map
• Drag and drop both Latitude and Longitude columns on the Map

The issue is fixed.

Power Query Issue with Supporting CLR type ‘Microsoft.SqlServer.Types.SqlGeography’

This issue happens when you want to import data from a SQL query which queries a column of type geography. Sometimes you need to get data from SQL Server by running SQL statements. For instance, in our sample database, look at the “Dimension.City” table. The “Valid From” and “Valid To” columns represent Slowly Changing Dimension which means not all the rows in the Dimension.City are valid. A best practice to keep a Power BI Model size optimum is not to import the data you need. It also improves the performance of our model. So I’m interested in importing just valid rows from Dimension.City table which are those rows with “Valid To” equal to “9999/12/31”.

• Get Data from SQL Server Database
• Enter “Server” and “Database” names
• Click “Advanced” then type the SQL statement below then click OK

SELECT *

FROM Dimension.City

WHERE Year([Valid To]) = 9999

• Look at the “Location” column

• It brought “Error”, but, why? Click “Edit” to see what’s wrong with “Location”
• Click on a cell with error

We got “DataSource.Error: We don’t support CLR type ‘Microsoft.SqlServer.Types.SqlGeography’” error. Hmm, it doesn’t look nice. Well, let’s move forward and fix this issue as well.

Solution: Get “Latitude” and “Longitude” Properties from “Location” with T-SQL

We can easily fix this issue by modifying our T-SQL a wee. I explained that we can get “Latitude” and “Longitude” from geography data type in SQL Server earlier in this article. We use the same technique to fix this issue. All you need to do is to call “Lat” and “Long” properties of “Location”. Hence, the SQL statement will be something like this:

SELECT      [City Key],

                     [WWI City ID],

                     City,

                     [State Province],

                     Country,

                     Continent,

                     [Sales Territory],

                     Region,

                     Subregion,

                     Location.Lat AS Latitude,

                     Location.Long AS Longitude,

                     CONCAT(Location.Lat

                            , ‘, ‘

                              , Location.Long

                              ) AS Location,

                     [Latest Recorded Population],

                     [Valid From],

                     [Valid To],

                     [Lineage Key]

FROM   Dimension.City

WHERE  (YEAR([Valid To]) = 9999)

 

To replace the old SQL statement with the new one just click on the gear icon () on the right side of “Source” step from “Query Settings” pane.

Click OK then click “Close & Apply” to get the correct results with no errors. The only difference in the result set is that we now have two more columns showing latitude and longitude. We concatenated latitude and longitude to support “Location” as well.

In the next section I address the same issues in Power View and Power Map.

Data Preparation for Power View and Power Map (Excel 2016 and 2013)

There are various ways to prepare data for Power View and Power Map in Excel 2016.

• Import data from SQL Server into Excel directly
• Import data from SQL Server into Power Pivot model directly
• Connect to SQL Server, import and transform data using Power Query, then load the results to either Excel sheets or Power Pivot data model

To keep this part as simple as possible I explain load data into Power Pivot directly.

• Open Excel 2016
• Click “Manage Data Model” from “Data” tab on the ribbon

• Get external data by clicking on “From Database” then “From SQL Server”

• Enter Server name then select “WideWorldImportersDW” database then click “Next”

• Choose how to import the data (stick with the default) then click Next

• Select “City” and Sales” from the list the click “Finish”

Note: If you use Excel 2013, don’t worry as it’s not that different from Excel 2016. You just need to import data using Power Pivot tab on the ribbon

Map Issues In Power View and Power Map

Wrong Cities in Power View and Power Map

The same thing happens in Power View and Power Map. When you put “City” on the map it shows wrong cities. It’s trivial as all of these tools are using Bing Map engine to visualise map data.

Here is what we get in Power View:

The screenshot below is from Power Map:

Solution: Concatenating Country and City in Power Pivot

We have concatenated Country and City in Power BI by writing a very simple DAX expression. The principle applies to Power Pivot.

Here is what we get in Power View and Power Map after we add a new calculated column that concatenates Country and City columns Power Pivot.

Power View results:

Power Map results:

Power Pivot Ignores the Columns with Geography Data Type

Just like what we faced in Power BI when you wanted to import a SQL query results into Power BI, if we want to do the same in Power Pivot we see that Power Pivot simply ignores the “Location” column when we write the following query in Power Pivot:

SELECT *

FROM Dimension.City

WHERE Year([Valid To]) = 9999

Here is what we get in Power Pivot:

As you see there is no “Location” column.

Again the solution is getting “Latitude” and “Longitude” properties from “Location” with T-SQL. So click “Table Properties” from “Design” tab from the ribbon in Power Pivot and modify the SQL statement as below:

Conclusion

If you are working in a project that involves with geospatial data and your source database is SQL Server then be conscious of incompatibility of Power BI, Power Pivot and Power Query with SQL Server geography data type. I’ll probably get wrong cities in Power BI map visualisations, Power View map or Power Map visualisations if you do not specify which city exactly you want.

Facing the above issues for the first time without knowing how to fix them could be annoying and costly. So I hope Microsoft fix these sort of issues in the next release/updates of Power BI and Excel 2016.

If you faced any other issues it would be great if you share it with us. So why not writing your story in the comments section below?