How to Do Power BI Mapping With Latitude and Longitude Only

2017-07-12_14h03_00

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;

1

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:

2

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

3

Now go back to Report tab. This time you will see these two fields with map icons besides them.

4

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;

2017-07-12_14h03_00

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 Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
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.

38 thoughts on “How to Do Power BI Mapping With Latitude and Longitude Only

  • 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 …

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

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

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

  • 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,

  • 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

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

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

Leave a Reply