We are all used Google location for whenever we travel into the new areas or find exactly where we are. So we simply turn on the location in our mobile. As soon as we turn on the location google will track our location with exact Latitude and Longitude. Infact google will track our lat and lon for every 3-5 seconds. Considering this amount of data for every one across the world, it’s pretty big.
- Google for Data
- Microsoft for Technology
Before diving into this blog further let see how the final report will look like,
We can download our all location history data from google as a JSON/KML Format from the Google takeout portal.
Google Takeout Link
By default the takeout portal will select all the data, but we really want to use the location for now. So deselect all the products and select the Location and we can choose the format as JSON, Later we will transform this JSON data in POWERBI for visualization.
Finally Click archive button to start download your data.
Google will send a mail once our data is available to download.
After that, download the file. We can see a similar JSON like below with our OWN LOCATION DATA
Open the PowerBI Desktop and load the Data as your Location History.json file
Once we loaded the PowerBI Desktop will automatically open the query window
Now we are going to Transform these data into Plotting into the Maps. For that we need 3 main Values
Click the List from main the window, it will create a new Step in the Applied Steps called Navigatation
Then you will ask the menu like Enter delimiter and How to handle the extra columns leave as the default values we don’t need to care about that
After these step our query window look like the below
You will prompt the menu like which columns you should choose to expand, from that just enable the timeStampMs,latitudeE7 and longitudeE7. Then Remove the default column name prefix
Now your query will looks like below
The latitude and longitude needs to divide by 10000000 in order to exactly plot on maps, we are now going to transform these two columns by dividing by 10000000. For these type of complex operation we can use the Advance Editor and forumlate our own DAX Power Queries.
You will see all our previous step there like Converted to table and Expanded Column1.Below the last step we are going to add the below Forumla
Make sure you put the comma (,) at the end of the Expanded Column1 step and update the line after the in statement from Expanded Column1 to Transformed the latlon to avoid the syntax error(s)
You will see the editor like below, highlighted the new lines
Click Done and you will see your queries like updated as below
So for upto Step 4 we got the latitude and longitude now we are going to convert the timestampMs in to datetime before doing that we need to make sure that our Timestamp is in BigInt (WholeNumber) type. By default PowerBI will analyse each column and assign the data type accordingly sometime we need to convert this for our transformation. In this case PBI will assign the type as text numeric (ABC123) for the timestamp column. So we need to convert this into Whole Number now
After applied this step you will see the timestamp is changed into like below
Now we are going to transofrm the timeStamp into DateTime,for this we again going to use the Advance Editor option
Add the below forumale as the last step before in
Your Editor now looks like the below, highlighted the new lines
Now you will get the result as below
Which will gives you the result as
We are all set with the data, now we are going to rename the columns for better understanding.
Finally you will get the result as below
We are all set with our data now. It’s time to make the visualize it on Maps
You will be in PBI Canvas now, drag and drop the Map Visualization from your right side menu into the canvas. Now we are going to map the fields from our location history data into the map visualdata.
Drag the latitude and longitude from fields into Map’s latitude and longitude
Now select the empty canvas, drag and drop the Table visualization into the canvas. Then Include the Date Fields into the Values
Remove the Quarter and Days from the Values,we don’t really need them inthe filter.
we are all set now to save and publish our own location visualization report into powerbi workspace.
Great ! You now published your own location data into PowerBI Workspace. Come let’s check it out in the powerbi portal
Go the https://app.powerbi.com
Select your workspace then view and play your report
Play the below GIF in New Window