cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
soheil Frequent Visitor
Frequent Visitor

Power BI Retrieves Wrong Latitude and Longitude from SQL Server Geography Data Type

Hi guys,

 

I reported an issue to Power BI Support team before posting it here. I received a reply saying the issue is related to Power Query and Power Query is not supported through basic support. As an option I'm told to post the issue in the Power BI community forum.

BTW, the issue (bug) is that Power Query automatically converts SQL Server data with geography data type to string. For instance, have a look at the "Dimension.City" table from the new SQL Server sample database, WorldWideImportersDW. Look at "Location" column which is a geography column looks like this in SSMS:

image-77[1].png

As you see I got Latitude and Longitude from the "Location" column.

If you import the same table in Power BI Desktop or Power Query in Excel this is what you get:

 

2016-06-24 07_26_00-.png

If you look at the two results you quickly find out that latitude and longitude data is incorrect in Power Query/Power BI Desktop:

image-78[1].png

 

I wrote a blog post showing a workaround here, but, facing the above issue without knowing how to fix it could be really costly in  a real project.

So I believe Microsoft/Power BI support team need to take reported bugs more serious.

Thanks

http://www.biinsight.com
3 REPLIES 3
Moderator v-sihou-msft
Moderator

Re: Power BI Retrieves Wrong Latitude and Longitude from SQL Server Geography Data Type

@soheil

 

What do you mean "incorrect"? Based on your screenshot, the POINT() shows same Latitude and Longitude values in your source table.

 

Regards,

soheil Frequent Visitor
Frequent Visitor

Re: Power BI Retrieves Wrong Latitude and Longitude from SQL Server Geography Data Type

It's clear in the screeshot that latitude and longitude are reversed. For instance, it should be point(42.1083969, -78.651695) rather than point(-78.651695, 42.1083969).
http://www.biinsight.com
Highlighted
ps30 Occasional Visitor
Occasional Visitor

Re: Power BI Retrieves Wrong Latitude and Longitude from SQL Server Geography Data Type

I have also confirmed this mapping bug with the geography data type with Azure/SQL.  Power BI assumed that POINT(Long, Lat) was the more normal format of Lat,Long.  Here is a direct link to an Azure table in the most current version of Power BI desktop is mapping the reverse of the the geography data. Should be 47,-122:

 

Capturegeo.PNG

 

The DirectyQuery from the SQL table is correct, but when you plot on a Map in Power BI the Lat/Long is not correct.  Is there a fix for this, or do we need to modify the query and extract out the Lat/Long.