Power BI- Route Map

Hey Guys, It’s been awhile :p

So, I was working on a little project where I wanted to show a proper route that was mapped correctly (much similar to google maps) on Power BI, however there was no visual supporting it at the time. As always, I looked into alternatives for this and I found a visual called route maps on the market place by Weiwei Cui.

Figure 1: Route Map - PBI Marketplace

I was also able to find the guide for this visual (link below)

https://weiweicui.github.io/PowerBI-Routemap/

This did not quite do the trick as it showed the displacement of the two inserted locations and not the route to it.

Figure 2: Incorrect Route (Displacement)

I was getting a little annoyed this point, so I googled alternate options and I found out these things.

1. The ability to create customs points on google maps
2. The ability to export it in KML/KMZ Format
3. The ability to convert it to an Excel file

So, I put all of this together and created my own route on the route map. 

I followed the steps below,

1. I took the start point and end point and looked for possible routes
2. I plotted the routes on my custom map on google maps 

Pre-requisites 

• Google Account

It will look something like this

Figure 3: My Maps Plot

    3. Exported the KML File

              a. Click on the hamburger icon next to map name -> Click on “Export to KML/KMZ” (you                      can get information like Longitudes, Latitudes.etc

Figure 4: Export to KML

     4. Convert KML file to xlsx using the following website for free

https://mygeodata.cloud/converter/kml-to-xlsx

     5. Give each point an ID and arrange data accordingly to look something like this

RoutePointID	Latitude	Longitude	City	Country	RouteID
1	6.917732	79.894484	Battaramulla	Sri Lanka	1
2	6.9166207	79.894274	Battaramulla	Sri Lanka	1
3	6.9166367	79.8940594	Battaramulla	Sri Lanka	1
4	6.9161521	79.8940004	Battaramulla	Sri Lanka	1
5	6.9160136	79.8939361	Battaramulla	Sri Lanka	1
6	6.9154172	79.8932011	Battaramulla	Sri Lanka	1
7	6.9152894	79.8930617	Battaramulla	Sri Lanka	1
8	6.9150923	79.8931153	Battaramulla	Sri Lanka	1
9	6.9148314	79.8931421	Battaramulla	Sri Lanka	1
10	6.9142615	79.8930509	Battaramulla	Sri Lanka	1

    6. Import the file to Power BI Desktop

    7. Change the data category of the longitude and latitude columns to “longitude” and “latitude”

               a. Go to the data tab on the left pane -> Click on the modelling tab on the top pane -> click                       on the relevant column -> click data category and select the relevant

Figure 5: Changing Data Category

     8. Follow the steps mentioned in the route map blog (link mentioned above) to insert the correct             field into the correct criteria

          

You will end up with something like this

Figure 6: Correct Route












This may look like a tedious task, but it gets the job done and to quite a satisfactory level.

Hope this came in handy for those of you who are struggling to get a proper route on a map.