PowerBI: Tracking Location using Google Location Data
Introduction
Google Location Tracking
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.
What is this article about ❔
In this article we will see how we visualize our own location data from Google using the Microsoft PowerBI Tool. So, this article is going to combine the power of two big Shots
- Google for Data
- Microsoft for Technology
Before diving into this article further let see how the final report will look like,
Getting the Location Data from Google
We can download our all location history data from google as a JSON/KML Format from the Google takeout portal.
Google Takeout Link
https://takeout.google.com/settings/takeout
Note! From the Google Takeout portal you can download all your product data which was gather by Google. Data like *Contacts,Calendar,Mail,Google Photos etc.,
*
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
**
**
{
"locations": [
{
"timestampMs": "1552134887205",
"latitudeE7": 130278368,
"longitudeE7": 802217804,
"accuracy": 105,
"activity": [
{
"timestampMs": "1552134952396",
"activity": [
{
"type": "STILL",
"confidence": 100
}
]
},
{
"timestampMs": "1552135133012",
"activity": [
{
"type": "STILL",
"confidence": 100
}
]
},
{
"timestampMs": "1552135313217",
"activity": [
{
"type": "STILL",
"confidence": 100
}
]
},
{
"timestampMs": "1552135494149",
"activity": [
{
"type": "STILL",
"confidence": 100
}
]
}
]
}
]
}
Visualizing the Location JSON Data using PowerBI Desktop
We have the data we want, now we are going to import the JSON file into PowerBI
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
- Latitude
- Longitude
- Date
So let's go and see how we can do this
Transforming the Data using PowerBI
✔ Step 1: Navigation
Click the List from main the window, it will create a new Step in the Applied Steps called Navigatation
✔ Step 2: Convert To Table
Right Click the List -> To Table Option
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
✔ Step 3: Exapand the Columns
Now we are going to exapnd the columns from the Record Table. For this
Go to
Transform menu -> Expand Option
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
✔ Step 4: Transform the Latitude & Longitude
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.
Go to
Home -> Advanced Editor
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
#"Transformed the latlon" = Table.TransformColumns(#"Expanded Column1", List.Transform({"latitudeE7","longitudeE7"}, each {_, (inner) => inner/10000000, type number}))
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
let
Source = Json.Document(File.Contents("C:\Jay\Location History.json")),
locations = Source[locations],
#"Converted to Table" = Table.FromList(locations, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"timestampMs", "latitudeE7", "longitudeE7"}, {"timestampMs", "latitudeE7", "longitudeE7"}),
#"Transformed the latlon" = Table.TransformColumns(#"Expanded Column1", List.Transform({"latitudeE7","longitudeE7"}, each {_, (inner) => inner/10000000, type number}))
in
#"Transformed the latlon"
Click Done and you will see your queries like updated as below
✔ Step 5: Change the Type of TimeStamp
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
Right Click the column timeStampMS-> Change Type - > Whole Number
After applied this step you will see the timestamp is changed into like below
✔ Step 6: Transform timestamp into DateTime
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
#"Transformed into DateTime" = Table.TransformColumns(#"Changed Type", {{"timestampMs", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,_/1000), type number}})
Your Editor now looks like the below, highlighted the new lines
let
Source = Json.Document(File.Contents("C:\Jay\Location History.json")),
locations = Source[locations],
#"Converted to Table" = Table.FromList(locations, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"timestampMs", "latitudeE7", "longitudeE7"}, {"timestampMs", "latitudeE7", "longitudeE7"}),
#"Transformed the latlon" = Table.TransformColumns(#"Expanded Column1", List.Transform({"latitudeE7","longitudeE7"}, each {_, (inner) => inner/10000000, type number})),
#"Changed Type" = Table.TransformColumnTypes(#"Transformed the latlon",{{"timestampMs", Int64.Type}}),
#"Transformed into DateTime" = Table.TransformColumns(#"Changed Type", {{"timestampMs", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,_/1000), type number}})
in
#"Transformed into DateTime"
Now you will get the result as below
✔ Step 7: Change the Type of timestamp
Now we need to conver the type of timestampMs into Date
Right Click timeStampMs -> Change Type -> Date
Which will gives you the result as
✔ Step 8: Rename the Columns
We are all set with the data, now we are going to rename the columns for better understanding.
Right Click the timeStampMS ->Rename -> Change it into Date
Right Click the latitudeE7 -> Rename -> Change it into latitude
Right Click the longitudeE7 -> Rename -> Change it into longitude
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
Click the Home -> Close & Apply
PowerBI Map Visulization
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.
Publish into PowerBI WorkSpace
we are all set now to save and publish our own location visualization report into powerbi workspace.
File - > Save -> Name the file as : My Location Report
Home -> Publish -> Select the workspace you want to publish
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
Source Code
You can download the PowerBI template from here https://gallery.technet.microsoft.com/PowerBI-Google-Location-0a6f3b57
Conclusion
We are all daily consuming GB's of data in form of browsing,Location Histories,Likes,Comments,Clicks etc., If we are all try to use our own data efficiently and in correct way using the technologies in-hand then all the things will become easy to us. Like said, in this article we see how we can use our own data to visualize things in a better way.
https://c.statcounter.com/11964936/0/3132c1d3/0/