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

[Anonymous]

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:

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:

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

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

[v-sihou-msft]

@Anonymous

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

Regards,

[Anonymous]

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).

[ps30]

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:

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.

[bsimser]

This is still broken. If you bring in a geography column directly into Power BI the lat/lon are backwards. Maybe this is only for the northern hemisphere?

The fix you have to do is put in the query into your DAX when retrieving the source something like this (column names will vary with your data):

= Sql.Database("server_name", "database_name", [Query="SELECT [Id]#(lf),[GeoLocation].Lat as Latitude#(lf),[GeoLocation].Long as Longitude#(lf) FROM [table_name]"])

Now you'll have a lat/lon field in your dataset with the right values.

[Anonymous]

Hi @bsimser ,

The issue is not southern/northern hemisphere. 

To fix the issue have a look at this blogpost:

https://biinsight.com/how-to-overcome-map-related-issues-in-power-bi-power-view-and-power-map/

Cheers