Skip to main content

Expanding Google Location History in PowerBI

Expanding the Google Location History in PowerBI

This page is supplemental detail to this blog... about how I used PowerBI to crack open the JSON files from Google Location History.

This first part reads all of the JSON folders from the folder in the first line

Source = Folder.Files("C:\[!!Folder where you put your Google Location JSONs!!]"),

#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),

    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),

    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Value", type any}}),

    #"Expanded Value" = Table.ExpandListColumn(#"Changed Type", "Value"),

The remainder expands all of the packed JSON data


    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"activitySegment", "placeVisit"}, {"Value.activitySegment", "Value.placeVisit"}),

    #"Expanded Value.activitySegment" = Table.ExpandRecordColumn(#"Expanded Value1", "Value.activitySegment", {"startLocation", "endLocation", "duration", "distance", "activityType", "confidence", "activities", "waypointPath", "simplifiedRawPath"}, {"Value.activitySegment.startLocation", "Value.activitySegment.endLocation", "Value.activitySegment.duration", "Value.activitySegment.distance", "Value.activitySegment.activityType", "Value.activitySegment.confidence", "Value.activitySegment.activities", "Value.activitySegment.waypointPath", "Value.activitySegment.simplifiedRawPath"}),

    #"Expanded Value.activitySegment.startLocation" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment", "Value.activitySegment.startLocation", {"latitudeE7", "longitudeE7", "sourceInfo"}, {"Value.activitySegment.startLocation.latitudeE7", "Value.activitySegment.startLocation.longitudeE7", "Value.activitySegment.startLocation.sourceInfo"}),

    #"Expanded Value.activitySegment.endLocation" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment.startLocation", "Value.activitySegment.endLocation", {"latitudeE7", "longitudeE7", "sourceInfo"}, {"Value.activitySegment.endLocation.latitudeE7", "Value.activitySegment.endLocation.longitudeE7", "Value.activitySegment.endLocation.sourceInfo"}),

    #"Expanded Value.activitySegment.startLocation.sourceInfo" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment.endLocation", "Value.activitySegment.startLocation.sourceInfo", {"deviceTag"}, {"Value.activitySegment.startLocation.sourceInfo.deviceTag"}),

    #"Expanded Value.activitySegment.endLocation.sourceInfo" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment.startLocation.sourceInfo", "Value.activitySegment.endLocation.sourceInfo", {"deviceTag"}, {"Value.activitySegment.endLocation.sourceInfo.deviceTag"}),

    #"Expanded Value.activitySegment.duration" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment.endLocation.sourceInfo", "Value.activitySegment.duration", {"startTimestampMs", "endTimestampMs"}, {"Value.activitySegment.duration.startTimestampMs", "Value.activitySegment.duration.endTimestampMs"}),

    #"Expanded Value.activitySegment.activities" = Table.ExpandListColumn(#"Expanded Value.activitySegment.duration", "Value.activitySegment.activities"),

    #"Expanded Value.activitySegment.activities1" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment.activities", "Value.activitySegment.activities", {"activityType", "probability"}, {"Value.activitySegment.activities.activityType", "Value.activitySegment.activities.probability"}),

    #"Expanded Value.activitySegment.waypointPath" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment.activities1", "Value.activitySegment.waypointPath", {"waypoints"}, {"Value.activitySegment.waypointPath.waypoints"}),

    #"Expanded Value.activitySegment.waypointPath.waypoints" = Table.ExpandListColumn(#"Expanded Value.activitySegment.waypointPath", "Value.activitySegment.waypointPath.waypoints"),

    #"Expanded Value.activitySegment.waypointPath.waypoints1" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment.waypointPath.waypoints", "Value.activitySegment.waypointPath.waypoints", {"latE7", "lngE7"}, {"Value.activitySegment.waypointPath.waypoints.latE7", "Value.activitySegment.waypointPath.waypoints.lngE7"}),

    #"Expanded Value.activitySegment.simplifiedRawPath" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment.waypointPath.waypoints1", "Value.activitySegment.simplifiedRawPath", {"points"}, {"Value.activitySegment.simplifiedRawPath.points"}),

    #"Expanded Value.activitySegment.simplifiedRawPath.points" = Table.ExpandListColumn(#"Expanded Value.activitySegment.simplifiedRawPath", "Value.activitySegment.simplifiedRawPath.points"),

    #"Expanded Value.activitySegment.simplifiedRawPath.points1" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment.simplifiedRawPath.points", "Value.activitySegment.simplifiedRawPath.points", {"latE7", "lngE7", "timestampMs", "accuracyMeters"}, {"Value.activitySegment.simplifiedRawPath.points.latE7", "Value.activitySegment.simplifiedRawPath.points.lngE7", "Value.activitySegment.simplifiedRawPath.points.timestampMs", "Value.activitySegment.simplifiedRawPath.points.accuracyMeters"}),

    #"Expanded Value.placeVisit" = Table.ExpandRecordColumn(#"Expanded Value.activitySegment.simplifiedRawPath.points1", "Value.placeVisit", {"location", "duration", "placeConfidence", "centerLatE7", "centerLngE7"}, {"Value.placeVisit.location", "Value.placeVisit.duration", "Value.placeVisit.placeConfidence", "Value.placeVisit.centerLatE7", "Value.placeVisit.centerLngE7"}),

    #"Expanded Value.placeVisit.location" = Table.ExpandRecordColumn(#"Expanded Value.placeVisit", "Value.placeVisit.location", {"latitudeE7", "longitudeE7", "placeId", "address", "name", "semanticType", "sourceInfo"}, {"Value.placeVisit.location.latitudeE7", "Value.placeVisit.location.longitudeE7", "Value.placeVisit.location.placeId", "Value.placeVisit.location.address", "Value.placeVisit.location.name", "Value.placeVisit.location.semanticType", "Value.placeVisit.location.sourceInfo"}),

    #"Expanded Value.placeVisit.duration" = Table.ExpandRecordColumn(#"Expanded Value.placeVisit.location", "Value.placeVisit.duration", {"startTimestampMs", "endTimestampMs"}, {"Value.placeVisit.duration.startTimestampMs", "Value.placeVisit.duration.endTimestampMs"}),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded Value.placeVisit.duration",{"Value.activitySegment.simplifiedRawPath.points.latE7", "Value.activitySegment.simplifiedRawPath.points.lngE7", "Value.activitySegment.simplifiedRawPath.points.timestampMs", "Value.activitySegment.simplifiedRawPath.points.accuracyMeters"}),

Popular posts from this blog

The Tectonic Speed of Government, Part 1: Procurement

This post is my reaction to conversations about how hard it is to create change in government, and how government projects (and IT projects in particular) take so long from genesis to completion. This is part 1, about procurement; part 2 will address project implementations . PS - there was a surprise Part 3 of this series later! Warning : what follows is an “inside baseball” discussion of government IT procurement. I’m not trying to dissuade you from reading it, but if you’re not enmeshed in this world you might want to consider reading my articles on lighter topics like organizing your electronic life , the greatness of Abbey Road , or the story behind The Room . If you ARE enmeshed in this topic, then please don’t overlook my call to action at the end! Changing the process will take a group effort, and I’m hoping to get feedback on my scheme to create a library of reusable software specifications. By the way, this post’s first title was “The Glacial Speed of Government” bu

The Tectonic Speed of Government, Part 4: Momentum

https://www.themoscowtimes.com/2019/11/20/meet-ivan-savkin-russias-human-mountain-a68246 This “Tectonic Speed” series is about why Government IT Projects take such a long time. The name refers to tectonic plates, rubbing against each other. No visible movement for a while then… CRACK! Government change is like that; it can take a long time to build, but when it happens it can be intense.  For more on that here is my 20:50 speech on this theme from the Code for America Summit 2020 , which turned into a virtual event. (I can tell you that it’s 20:50 because of the PechaKucha-ish format: 25 slides for 50 seconds each.) ************************************************************************** Disclaimer: All of the ideas expressed in this article are my personal statements and opinions, and do not reflect the opinions/statements of the City of Urbana. ************************************************************************** One impact of the COVID-19 pandemic is a shake-up of p

But First... Let Me Take a Selfie

Excel 3D Map of my drives home. Colors are waypoints and height is the trip duration. Read on to see what the hell this is. This piece began before the pandemic, but the use of our phones as tracking devices for contact tracing prompts tough questions about privacy and tracking that make this timelier than I expected. See the middle section for thoughts on this. *********************************************************************************************** Disclaimer: All of the ideas expressed in this article are my personal statements and opinions, and do not reflect the opinions/statements of the City of Urbana. *********************************************************************************************** “OK Google… Track Me All Day Long”  Google Location History logs your every move, every day – if you turn it on. (To their credit, it’s off by default.) It’s worth asking - why would anyone want to do this?  Well, I did it for a few years because I was curious about the r

Why the Tectonic Speed of Government?

The original name ("Glacial Speed of Government”) is both cliché and inaccurate, as it implies a faster pace than it used to. I decided that “Tectonic Speed” is more accurate because change in government shows tremendous resistance and moves slowly, but when it happens progress can occur in significant outbursts - and in those moments, there is great opportunity!     Click on "Read the Whole Thing" to access these links: Part 1 |  Part 2   |  Part 3  | Part 4 | Wait there's a Video?!  

How To Videos: Lucity Queries with Microsoft SQL Server and Excel

What follows is not a blog, but some suggestions on using Microsoft SQL Server "Views" to query your Lucity data using Excel.   This information is intended to assist Lucity software users, and not for any nefarious purposes. I recommend watching the videos in Full Screen view and with HD resolution.  They're not as blurry as they look on this page!!  Each of these about two minutes long, but the original actions only took 50 seconds each.  (After recording them, I decided to slow them down to make them more watchable.) 1. How to create a SQL Server "View".   The video shows how to create a new View from the core Work Order table.  (WKORDER - see the data dictionary here .)  The video first shows the simple method of creating a view with all fields, then shows the more effective method of including only needed fields, and re-labeling them with their on-screen names. Music: "A View to a Kill" - Duran Duran 2. How to Connect to the Vi