You may have seen many videos or blog posts so far that Power BI Desktop showed the data on the map visualization based on address, suburb, city, state, and country. Fortunately Bing Map helps a lot to search the point on the map based on address fields. However sometimes you don’t have address fields, actually in some types of the data there is no address field. As an example an earth quake most of the time happens somewhere deep in the ocean where there is no street address! All you have is latitude and longitude as the Geo graphical data. However latitude and longitude is precise enough for any GPS device to point out the exact location. Unfortunately to your surprise, at this point of the time Power BI Desktop map doesn’t support visualization only based on latitude and longitude! So this post is about how to visualize it with Power BI Desktop map easily.
Problem Definition
As I’ve mentioned there are sometimes that you have no address information but only latitude and longitude, an earth quake data is one of the most common examples of that. Here is example earth quake data that I’ve fetched from Quake Search service of GeoNet website. This website search through all earth quakes happened in New Zealand. It can also export the data as CSV file, which is the file that I’ve used. Screenshot below shows part of the data set as an example;
As you see in the screenshot there are two highlighted columns for latitude and longitude and also a column for magnitude. The file is for a year earth quake data (from September 2014 to September 2015), and it has 19K records. Wow, that’s a lot of earth quakes isn’t it? but don’t worry most of them are on minor magnitude, this file contains records with even 0.7 as magnitude! such an awesome recording of events.
Now I want to visualize this data on the Power BI Desktop Map to see whereabouts most of earth quakes are happening! If I fetch that data into Power BI through Get Data and then from CSV experience, and load it without any change in the Power BI Desktop I’ll see then in the fields section like this:
As you see Power BI considered these fields as numeric fields and summarized them in the view (you can see sigma icon besides them in screenshot above). This can be easily changed in the data tab. Select the Column and then change the Data Category under the Modeling menu to respective field (latitude or longitude).
Now go back to Report tab. This time you will see these two fields with map icons besides them.
Now let’s create a Map. Map works with Latitude and Longitude (which we have fortunately), I also add magnitude as values. For chart below I’ve also showed magnitude in values, and color saturated it based on depth of the earth quake. as the data rows were too many (19K quakes), I’ve filtered to show only those that has magnitude greater than 6. Here is the result;
Map still might seems scary for you to see that many earth quakes with more than 6 magnitude in only one year, But if you wanna live in a magnificent country you should take some risk 😉
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Wow, this is a great workaround!
I’m particularly interested in spatial data on SQL Server platform. I’ve spoken on SQL Saturdays and wrote articles on this topic.
So far, I didn’t try to play with spatial data and maps on Power BI. I thought the product wasn’t suited for mapping as Reporting Services is . But now, after your post, could be the time to take some tests!
A lot of earthquakes in Italy too …
Thanks Andrea for kind words.
Would love to see your new articles on this topic 🙂
Cheers,
Reza
Thank you Reza, you really dug us out of a hole here. We were plotting distances travelled by tutors onto a map showing the location of our courses and, although we were providing the long and lat co-ordinates, Bing was unable to find all the UK postcodes as the location (despite our matching the postcodes to get the long/lat co-ordinates to start with). Your fix has moved our charts firmly out of the Arctic and made the process relatively painless.
Hi John,
Thanks for letting me know about your scenario. I’m glad that it was helpful
Cheers,
Reza
Thanks Reza – that’s perfect!
Thanks John for your kind words 🙂
Cheers,
Reza
I tried this tip, the map stills complains “More location data is required to create a filled map”
Latitude, Longitude are correct, which I verified in bing search
It is hard to figure out
Hi,
Have you tried with simple MAP instead of Filled Map to see what is the result?
Hi Reza,
I think you’ve misunderstood what the Location field is actually for when using lat/long. It just acts as a unique ID to define the grain that the data is rolled up to (aggregated) for display.
In your case concatenating lat/long makes it work just because your quakes also have a (probably) unique lat/long, but it should also work if you put your ‘public ID’ in your location field instead. AFAIK the webservice lookup only applies if you don’t have your own lat/long information. So this technique works, but not for the reason you think it does.
This confused the heck out of me to start with also, but it’s quite useful, because (given your data) it gives you two options:
– plot average magnitude for all quakes by location (what you are doing)
– plot magnitude for all quakes by quake id (potentially giving rise to multiple points in the same spot)
HTH
Reza Rad
It is really nice article and it really helped me out. Is there any way I can show custom icon/image on this map. For example I want to show the flag of country on map instead of dot or the built in icon.
Thanks
Hi Noor,
Thanks for your kind feedback.
adding custom image is not supported built-in at the moment unfortunately.
However there are some work arounds such as:
1- You can create your custom map as an image and put flag of countries in places you want to. then use Synoptic Panel custom visual and define regions on that and map region names to values in your data set.
2- create a custom visual your own with a map with this new feature (won’t be easy I believe. I haven’t built a custom visual myself yet, but it should be pretty scripting and coding expereince)
Shame, such a great article, this was working perfectly fine, but after the latest update, the map is just static, not pointing to the location, haven’t done anything extra, working on older version, upgraded and BAM… Not working anymore. Have u tried upgrading to see if yours works as well?
Hi Michelle,
Thanks for your kind feedback.
This still works for me correctly with the most up-to-date Power BI desktop version.
What do you mean by not working? does it gives you error or does it points to some other locations on the map?
Cheers,
Reza
Hi
Im trying to create a filled map for suburbs in Victoria, however Bing offers a pin – where as google offers the boundary map 🙁 any suggestions?
thanks
Nikki
Hi Nikki,
I’ve wrote another post about using Filled Map in Power BI:
https://radacad.com/power-bi-filled-map-the-good-the-bad-and-the-ugly
However you might not always get what you want with it.
Cheers,
Reza
This one doesn’t work for me: Text.From([#” latitude”])&”, “&Text.From([longitude])
What I did was this: Text.From([ latitude])&”, “&Text.From([longitude])
and I got the Point column, but still map is not zooming in.
Hi Michelle,
Thanks for your comment, Yes you are absolutely right, it there was typo in the script. I’ve fixed it now.
Regarding the zooming; When I get the map also it doesn’t zoom in, So I do zooming in myself, and then it remembers that.
Hopefully that get fixed soon in Power BI
Cheers,
Reza
Interesting, mine just doesn’t remember, it keeps zooming out every time I click on another city, I wish it would remember… dammm
Well, already sent a frown to Microsoft and the pibx and hope they will have an answer for that.
Having to have a unique location field as well as lat/long information is a limit that’s now been removed in the latest Power BI download:
https://powerbi.microsoft.com/en-us/blog/power-bi-update-lots-of-new-authoring-features-and-a-new-power-bi-desktop-update/
Do you also know how to change the color of a city on a map? Going to Data colors is not helping… When I set to another color, the change doesn’t take effect.
Strange that I haven’t notice that so far. No I can’t. It seems to be a bug. Not sure if it was in earlier versions. Thanks for pointing out Michelle.
Cheers,
Reza
Thanks for the pointers, however mine doesn’t seem to work, I am using version 2.34.4372.322) which is the latest as of now.
It doesn’t have the VALUES field, only has saturated, not sure whether that’s what is required to do the job.
Some of the coordinates are below
0.3088061,32.6209526
0.3087645,32.6209065
0.3088036,32.6210044
0.308773,32.6209068
0.30855308,32.62110251
0.3660917,32.6184312
0.3088075,32.6210089
0.3088165,32.6210077
0.3087745,32.6208882
0.308784,32.6208885
0.3088072,32.6209589
0.3088249,32.6210145
Am i missing something?
The Map still has that message ” More location data is required to create filled map”
Hi Mike,
Sorry for my late response. Been away in San Jose, and Liverpool for conferences.
It is strange that you don’t have Values place holder. Could you send me an screenshot or your Power BI file if it is not big? you can send it to reza at radacad
Cheers,
Reza
Hi Reza,
I need help with drilldown requirement from Area to Branch in power bi Map.
I have data in the below format
Area Branch OA_latitude OA_longitude Branch_latitude Branch_longitude
East B1 25.6259 48.6642 24.6259 45.6642
East B2 25.6259 48.6642 23.6259 46.6642
West B4 19.5434 40.1729 20.5434 38.1729
West B5 19.5434 40.1729 21.5434 38.1729
Central B6 25.2715 51.1877 26.2715 49.1877
Currently i have following properties setup for map visualization in power BI
Legend : Branch
Latitude : Branch_Latitude
Longitude : Branch_Longitude
Scenario: When Power BI report open map should show area wise values and when user clicks on area, map should drill down to branch and show values at branch level.
Problem : Since Area and Branch latitude and longitude are in different column i am not sure. How to change change the column in drill.
Already tried following:
I tried creating a measure which check for the current level and return respective latitude or longitude value but i think we can’t use measure in Latitude area on the map.
Request your input on this requirement.
Thanks in advanced.
Hi Abdul,
You can create a column which is a combined lat, long (similar to the example of this post) for Area, and one for branch.
Then put that filed in the Location field of map in a hierarchy (first area, and then branch). then using drill down feature of visual you can drill down to branch or up to area.
Cheers
Reza
Hi Reza,
So many thanks for the pointers suggestion… it’s solved our problem to use lat-long on power bi maps.
Best regards,
Thanks Eudis for letting me know. I’m glad it helped.
Cheers
Reza
Hi,
I am new to power bi, and very interested with mapping visualization.
I have question, can I use filled map (boundaries) while using latitude and longitude in it? I ‘ve been doing this with google my maps (combining boundaries kml with coordinates in a map) but cannot explore the analytics things when it comes to data visualization. thank you for your kindness, regards, R Munadji
Unfortunately not.
The filled map makes no sense with latitude and longitude. because latitude and longitude are pointing to a specific location, while the filled map shows a polygon and a region.
if you hare the boundaries, you can use Shape Map, or other types of maps such as ArcGIS and map custom visuals.
Cheers
Reza
thank you sir for your super quick response, it helps alot
Please, I start on power bi. I work on map and location in Ivory Coast. How do you write latitudes and longitudes for this country in the table? Which format to use for example for Abidjan 5 ° 20 ’11 “N, 4 ° 01′ 36” W? Thank you.
There are many web services and APIs that gives you lat and long based on the location. For example, http://bing.com/maps, can give you this if you search :
Côte d’IvoireVallee du Bandama
7.622842, -5.55256
Cheers
Reza
Hi,
I just want to ask a question and kindly share how to resolve it. So, here is my question what if I want to map two point location say point A and point B having given coordinate i.e latitude and longitude respectively. So I want to show these two points connected according to my user?
Hi Anand.
Are you looking for a route map? in that case, you can have a look at this custom visual.
Cheers
Reza
Hi @Reza Rad, What if I want to plot multiple points within one location i.e. there can be number of health facilities within a district or county… and how to randomize longitude and latitude to make them unique…
Hi Hassaan
It would be up to you how to present it.
Do you want to accumulate the result of multiple points into one using Sum/Avg etc? in that case, you just need to select that aggregation in the value of the chart.
Or do you want to show them separately? which in this case you can add another attribute (such as health facility name) in the legend.