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