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.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"}),