PowerBI Report with Google Location Tracking Data

[Jayendran]

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

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 you data is available to download.

After download your file. You can see a similar JSON like below with your 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 you 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 your 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  

Play the below GIF in New Window

My Location Report.pbix