Table of Contents

 

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
  1. Google for Data
  2. 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! 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.

Web Analytics 


See Also