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