There are limited choice in Power BI to display information in a map with directional flow – meaning show the direction of movement in a sequence of movement data. e.g. tour route, flight route, etc.
In this blog lets explore how this can be achieved in Power BI using the Route Map visual.
Route Map visual
Route map visual is from Weiwei Cui and can be downloaded from Power BI store.
As per the description in the site:
Route map is designed to visualize trajectories of objects, such as taxis, vessels, airplanes, and hurricanes. Specifically, a trajectory dataset often contains three aspects of information: time, geo-coordinate, and attribute. In this visual, you can draw the spatial-temporal information on a map with polylines, then use line styles (e.g., colors and widths) to encode desired attributes.
The sample “travel packages” dataset I used is as below:
|From||To||Type||Route ID||Route Order|
|United States||United Kingdom||Flight||PK1||2|
The above dataset cannot be directly used for Route Map visual.
1. From and To locations needs to be on separate lines.
2. Need to have geo-location code for the Country names.
Step 1 – From and To locations needs to be on separate lines
To transform the dataset so that we have separate rows for From and To data, we need to “Unpivot” the dataset.
Step 2 – Need to have geolocation code for the Country names
To get the geocodes for the countries I used the Get Data from Web.
You can find the geocodes from Google datasets here. I simple created a CSV file with data and loaded it to Power BI. Then I merged the “travel package” dataset and “country geocodes” datasets together.
Then expand the Tables in the resulting column.
For ease of copy and paste the final dataset is below:
|Type||Route ID||Route Order||Attribute||Value||Latitude||Longitude|
Map Route Visual
When you plot the above data in the Route Map visual, you get the below.
If you see the directional arrows in the above diagram it is not correct. There is no route from Australia to Canada but the map shows an arrow from Australia to Canada. My understanding is this is because the Route Map is ordering the rows differently similar to what is shown in Values table visual.
Similarly, if I select the Route ID PK1 from the Slicer, the route is displayed as Canada -> Singapore -> United Kingdom -> United States. This is in alphabetical order.
The actual route for Route ID PK1 is Canada -> United States -> United Kingdom -> Singapore.
Note: There is a bug with Route Map visual as it cannot display the arrows for the UK to USA and sometimes blanks out.
Additional Data Transformation To Fix the Problem
To fix the sort order problem I added an Index column in Query editor to the final dataset and added a new column in DAX as below:
Column = Routes[Index] & Routes[Value]
This will add anew column as:
Now in the Route Map visual I used the ‘Column’ as the value for the ‘Timestamp’.
Now the direction flow order problem is fixed (though the Route Map bug that it cannot display all the routes remain).
When I select Route ID PK1 now the route is displayed correctly with correct directional flow.