How to Do Power BI Mapping With Latitude and Longitude Only

Posted by on Sep 24, 2015 in Power BI | 29 Comments
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

10

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. But Map still doesn’t show anything, it looks for a location as you see in the message below!

5

Location is required to create a map. Click to learn more.

So without a location field it seems that Map doesn’t work, which is not what I expected!  Same thing happens with filled map (which is another type of map visualization in Power BI)

Solution

Well first I though of some hard ways, such as web services that I call with latitude and longitude to get a response. Then I’ve found that it is much easier than that. If you ever searched into Google Map or Bing Map with coordinates you know exactly what I mean. Yes, you can simply search coordinates in the bing map with having latitude, a comma, and then longitude.

6

So things get really easy now, Power BI Desktop uses Bing Map search, so I should expect same result in Power BI Desktop if I provide a string of latitude, a comma, and then longitude. So I’ve built a new column in Power BI to represent that. To build a new column click on Edit Queries to go to Power Query element of Power BI. Then under Add Column menu bar click on Add Custom Column

7

Name the new column as Point. and enter below expression as the column formula.

8

The formula above will generate a column with a text value as below:

9

Now in Query Editor window click on Close & Apply, after loading data into Power BI Desktop, I just use Point column in the location parameter for Map as you see in the screenshot below. I don’t need to provide latitude and longitude anymore. 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;

10

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

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

29 Comments

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

    • 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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">