Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
sjkp
Advocate II
Advocate II

How to map latitude longitude

I'm trying to plot latitude longitude on a map in power bi Desktop on a standard map.
In location field I'm unable to add both latitude and longitude. There are also two fields for latitude and longitude but they don't seem to work? Am I doing something wrong, or is it not possible to simply give the map the location to plot.

I can get it working if I use an adresse in the location field, but I'm trying to map positions at sea, so not really useful.

1 ACCEPTED SOLUTION

sjkp,

This one is a little counter intuitive. The only map tool that can take straight lat/long is Power Map in Excel, but there currently isn't any integration with the rest of the BI stack yet for that.

 

The way you do this in Power View - maps, or the PBI Desktop is put you lat and long in the appropriate Fields, then whatever it is you are examining in that location (count of wrecks, count of "x") that goes in the location and the value fields. Then your bubbles will show up in the middle of the ocean at the coordinates you specified in the lat/long.

If there aren't direct counts, I would assume you can just create a column to contain the value of 1 to make this work.

Here is a screenshot of the sample data I pulled in and the layout in PBI Desktop

Capture.JPG


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

30 REPLIES 30
gusly
Helper II
Helper II

I also found this quite confusing, was really hoping it would be more straightforward. Would be good with an example showing how to use latidude and longitude, but I haven't found any official one.

The real problem for me was Power BI Desktop being set to use a locale that uses "," (comma) as decimal separator. Before understanding I could change that setting, I could only achieve proper results by replacing "." with "," as decimal separator (and this wouldn't be a lasting workaround for me as I'm exporting data without that possibility). However, changing the setting saved me, but I didn't find that solution obvious.

I found a solution but I used the global map. It found all the locations:

 

Spreadsheet LayoutSpreadsheet LayoutAccurate Global MappingAccurate Global Mapping

 

Here's what I did:

 

1. I did change the numbers to decimal

2. I merged the fields to include city and location.(combined location, clocation)

3. I had two unique columns. One had unique dates. The other had Priority numbers that were unique.

For the two native map types there are different requirements.

 

A filled map can only take standard location names that define a region with boundaries. This is because there must be something to fill. Lat/Lon coordinates cannot be filled. They are points. How do you fill a point? The same for a full street address, or a street name (a line).

The two-dimensional geographic data types that can be used with the filled map are:

  • Postal Code
  • City
  • County
  • State or Province
  • Country / Region
  • Continent

These must be in a form that can be recognized by Bing maps. The field holding each of these values should have its data category set appropriately in the modelling ribbon menu. One of these fields (or more in a drill-down hierarchy) must be in the Location field well. The area bounded by this geographic data will be shaded based on the value of the measure used in the Values field well.
The Legend field well is optional, but will provide an alternate value in the tool-tip to identify the location. I honestly don't know why Longitude and Latitude are included in the filled map field wells, as I have never been able to use them or see them change the map - this would be interesting to get an explanation from the devs on.

 

The Map visualization can follow the same rules for Location as the Filled Map, but can also take point locations (e.g. street address).

 

The cool thing about the Map is that it doesn't *need* this geo data in the same way the Filled Map does. You can use Longitude and Latitude (averaged by default - we'll see why this is useful in a minute) to do all of the location plotting.

 

  • The Values field well is similar, except here it determines pie size.
  • Location can take any field (that is associated with the Lat/Lon fields we're using). Thus, we can put [StoreName] for example into the Location well, and it will be mapped to the average of Lat/Lon. Now, since we expect just one row in our geography dimension for a [StoreName], we get back exactly the Lat/Lon (average of a single number is that number).
    • Location can also take a hierarchy. We could put [StoreRegion] into the Location well and we'd see the region's pie circle at the location that is the average of all [StoreName]s' Lat/Lon coordinates. We could then drill down through [StoreRegion] to [StoreName] and see the child [StoreName]s plotted in their exact location.
  • Legend can be used to have an alternate value in the tooltip, or to introduce slices into the pie if each Location value has multiple Legend values associated
  • Color Saturation can be an arbitrary measure, whose value will determine the saturation of the individual pies plotted (default is dynamic, but it can be customized with hard-coded limits).

Legend and Color Saturation cannot be utilized together.

 

Anonymous
Not applicable

Thank you, this helped me to figure out why my maps wouldn't work! My maps are now working and I'm using post code with Filled Map and Long/Lat with the globe Map.

Thanks for the tips, Greggyb. In regards to county, I had to use "Howard County, MD" [County, State] or my data was off.

Also, do you know why it bubbles instead of filling in the county borders? EDIT: I got it. I used the regular MAP but "filled" is the border one.

 

Mapping by CountyMapping by County 

Filled Map by ZipcodeFilled Map by Zipcode

I couldn't pull up any city in Howard or Carroll County, Maryland though...weird! I'll open tickets with Bing Maps.

Ukraine I got done. I could do "city" filled map and a regular map. I put in the administrative centres and color coded the oblasts!

 

Capitals with color coded OblastsCapitals with color coded Oblasts

Filled Map by CitiesFilled Map by Cities

This is the result with just the original "Map"

 

Combined Locations ColoredCombined Locations Colored

Individually coloredIndividually colored

 

 

Anonymous
Not applicable

In addition to the hints I found here concerning setting data category to Longitude / Latitude and data format to decimal I got the expected picture when I set "Default Summarization" of Longitude and Latitude to "Maximum" (Take "Minimum" if you want, it does not matter as long as the summarization delivers the Longitude/Latitude values themselves - An "Only" function like in QlikView would be the correct choice for this purpose here)

In the location field of the map tool, I entered "Country" which does the trick. It does it before without Longitude/Latitude but I needed 5 seperate Regions in China, which I did noct achieve before. I have 5 different entries for them in "Countries" as well, so I think it's the combination of unique combinations of Country/Longitude/Latitude that makes it work.PowerBI_LongLat.png

I've encountered the same issue.

 

There is however a workaround. Add a column that concenates the latitude and longtitude values, together with a comma, eg: Location = [Latitude]&", "& [Longitude]. Map that column to the location field and don't mind using the latitude and longtitude field. That should do the trick.

 

Regards,

 

Richard van der Pool

It worked out!!! The only problem is that in tooltips the name of the destination is shown as the coordinates of latitude and longitude. Any suggestions in solving this problem??? Thanks.

 

Kevin Hernández

It worked out!!! But the problem I get is that the name shown of the location is the coordinates of longitude and latitude. Any suggestions on how to solve this? Thanks.

 

Kevin Hernández

Thank you Richard - this worked perfectly. Why we even need such a workaround is something of a mystery - would it not be one of the first things you'd test with a mapping visual?

When I use a concatenated lat/long in Power BI Desktop I get the following pop-up:

 

Subject: More location data is required to create a filled map

Detail: To create a filled map, the Location data should include Bing Map-supported geographic data, such as country/region, state/province, or postal code.

Terry M

I was able to get it working by:

1. Using a concatenated lat/long in the "Location" field (eg. "<latitude>,<longitude>", no quotes though)

2. Putting Latitude into the "Latitude" field

3. Putting Longitude into the "Values" field, NOT the Longitude field

4. Leaving the "Longitude" field empty

 

I don't know why it worked, but it did. 

 

PBIMapConfig.JPG

 

 

Terry M

sjkp,

This one is a little counter intuitive. The only map tool that can take straight lat/long is Power Map in Excel, but there currently isn't any integration with the rest of the BI stack yet for that.

 

The way you do this in Power View - maps, or the PBI Desktop is put you lat and long in the appropriate Fields, then whatever it is you are examining in that location (count of wrecks, count of "x") that goes in the location and the value fields. Then your bubbles will show up in the middle of the ocean at the coordinates you specified in the lat/long.

If there aren't direct counts, I would assume you can just create a column to contain the value of 1 to make this work.

Here is a screenshot of the sample data I pulled in and the layout in PBI Desktop

Capture.JPG


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

hey guys,

 

thanks fo rall the replies, the thread helps a lot. BUT my powerbi is having problem with my Longitude and Latitude, it is changing it automatically to AVERAGE Longitude and latitude when I drag it to their fields.

 

EmirSvemir_1-1651222868886.png

 

 

any solution to this? what do I do wrong? I checked my data set and the fields are marked as decimal numbers but also some fields have written NULL ...could this be the problem?

 

EmirSvemir_2-1651222991792.png

 

Thanks a lot in advance! Cheers,

 

emir

 

i changed null to 0, the long and lat are ot anymore changed to average BUT I still have no results.

 

EmirSvemir_0-1651227080379.png

 

Thanks Eno, your picture helped resolve my problem, I didn't catch that my Latitude and Longitude came in as text instead of decimal in my data model so that's why it wasn't working like I expected/used to in Excel. For some reason, Excel Power Query ingesting from the same data file recognized my values as decimial to begin with but Power BI Desktop did not. Odd.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

 Thank you for the date type decimal and data category tip

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.