Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 } ] } ] } ] }
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
So let's go and see how we can do this
Transforming the Data using PowerBI
Click the List from main the window, it will create a new Step in the Applied Steps called Navigatation
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
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
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
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
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
Now we need to conver the type of timestampMs into Date
Right Click timeStampMs -> Change Type -> Date
Which will gives you the result as
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
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.