Energy BI and Google Maps API (Tackle Lookup)

On this publish I clarify methods to use Google Maps APIs to retrieve helpful data out of Google Maps. The use case situation might be getting handle, postal code, and many others. from present latitude and longitude values. The information might be generated by any form of GPS monitoring system like your Garmin biking GPS pc, your Fitbit watch and many others. I do know you possibly can load your GPS monitoring information into athletic social networks to analyse your actions. However, if you wish to do some extra particular information analytics like through which space of town you created extra energy throughout your biking actions then these web sites won’t provide you with what you need without spending a dime.

For example, you possibly can export your system information to CSV then import and append all CSV recordsdata right into a Energy BI mannequin and create wonderful analytical experiences. Methods to import your CSV recordsdata right into a Energy BI mannequin is out of scope of this text so I depart it to you for any additional investigations.

GPS monitoring gadgets are creating plenty of information together with geographic coordinates which might be simply utilized in Energy BI. You’ll be able to merely put latitude and longitude on a Map visualisation and also you’re good to go.

You too can concatenate the latitude and longitude information and use it as Location in your Map visualisation.

This may be achieved from Question Editor in M language.

However, in some circumstances you want some extra geo-information like Nation, Metropolis, Put up Code and Avenue Tackle in a desk as effectively. Otherwise you may wish to use postal code in a slicer. On this article I present you methods to get all of those data out of Google Maps by passing present coordinates to Google Maps geocoding API.

To have the ability to comply with this publish steps you should:

Have a Google account
Activate the Google Maps Geocoding API
Discover a desired API in Google Builders Information
Use the API in Energy BI
A GPS system information exported to CSV file

I assume you have already got an present Google account or you understand how to create a brand new one, so I soar straight into the subsequent step which is activating geocoding API. To have the ability to begin utilizing the Google Maps Geocoding API you should get an API key first.

Scroll down and click on “Get A Key”

This can navigate you to Google Builders Console
Click on “Sure” then click on “Agree and proceed”

Sort in a reputation then click on “Create”

You efficiently created an API key. We are going to use this key within the subsequent sections of this text.

Now that we have now an API key we will use plenty of Google Geocoding APIs accessible right here. As you possibly can see there are many helpful APIs you need to use to leverage your Energy BI mannequin. In my pattern I have to retrieve Nation, Put up Code and Avenue Tackle out of present coordinates generated by my bike GPS pc. So I take advantage of Reverse Geocoding (Tackle Lookup). Here’s a pattern:

https://maps.googleapis.com/maps/api/geocode/json?latlng=-36.8484213,174.7457047&key=YOUR_API_KEY

In the event you copy and paste the above API in your internet browser you possibly can see its output which is a JSON offering detailed details about the placement.

Observe: Don’t forget to repeat and paste your API key on the finish of the API.

The thought is to outline a perform in Energy BI then go latitude and longitude to the perform to get Nation, Postal Code and Avenue Tackle.

The way it works

Open Energy BI Desktop
Get Knowledge from Internet
Paste the API within the URL part then click on OK

Stick to the default and click on “Join”

This opens “Question Editor”
Click on “Record”

Convert the checklist to desk by clicking “To Desk” button from “Remodel” tab from the ribbon

Click on on increase complicated column icon () to increase the desk then tick “formatted_address” and “varieties” columns then click on OK

Broaden the “varieties” column as effectively

Filter “varieties” column to indicate “Nation”, “Postal Code” and “Steer Tackle”

We have to the columns as rows so we have to transpose the desk. So go to “rework” tab and click on “Transpose”

As you see the second row incorporates the column names. Click on “Reverse Rows” to indicate the second row first

Click on “Use First Row As Headers”

Rename the question then save the mannequin

Create a Perform from an Current Question

Thus far we loaded the geo-location data for only one coordinate. Now we have to flip the question to a perform in order that we will invoke it for all coordinates we have now in our dataset.

A perform building is as under:

Click on “Superior Editor”

Change the code as per screenshot under

1– Perform inside title

2 & 4 – Enter parameters

3 & 5 – Parameters’ information varieties

6 – Perform physique

7– Output

Observe: The title that will probably be used to revoke the perform is the question title which is “fn_GeoLocation” in our pattern.

Proceed modifying the codes as under then click on “Carried out”

What we have now achieved above is that we changed the static latitude and longitude with parameters. We used “&” to concatenate each side of the supply.
As quickly as you click on “Carried out” Energy BI detects the question as a perform

To check the perform simply click on “Invoke” and enter latitude and longitude then click on OK

Delete the invoked perform step

We efficiently outlined a question as a perform in order that we will invoke it anytime.

The following step is to load GPS information from CSV. I’m utilizing my bike pc information that I exported to CSV format earlier.

Obtain pattern CSV file right here.

In Question Editor, click on “New Supply” from “Residence” tab then click on “CSV”

Browse and open the CSV file
Click on OK

Click on “Add Customized Column” from “Add Column” tab and title it “Geolocation”
Enter the next method to invoke the fn_GeoLocation perform and click on OK

Observe: I used “Quantity.ToText” perform to transform latitude and longitude information kind from quantity to textual content as we outlined the perform parameters as textual content.

You’ll get a “Info is required about information privateness” warning message. Click on “Proceed”

Choose a desired privateness degree from the checklist then click on “Save”

Observe: Study extra about “Privateness ranges” right here.

Scroll proper to seek out the brand new added column
Broaden “Geolocation” column

Voila! We’ve obtained all data wanted

Click on “Shut & Apply” from “Residence” tab from the ribbon and wait till the info masses into the mannequin.

Observe: It would take someday to load information into the mannequin primarily based in your dataset measurement. That is due to sending coordinates to Google Maps row by row then loading the outcomes into the mannequin, so the method might be gradual when you have a big dataset. There are additionally some limitations utilized to the API utilization.

Now you possibly can simply use new columns for various functions like placing Nation or Postal Code in slicers, making a Location hierarchy and so forth. So there are many totally different fascinating issues you are able to do with the knowledge we obtained from Google Maps.

Geo-Location APIs in Basic

As Google isn’t the one one who supplies geo-location APIs. You are able to do the identical utilizing Microsoft Bing Map or HERE Maps.

Extra studying supplies:

Limitations

Utilizing Geocoding API isn’t free so some limitations apply when you find yourself on a free utilization plan. For example you need to use solely 2,500 requests per day and 10 requests per second. Take a look at right here to be taught extra in regards to the API utilization limitations.

The opposite alternate options like Microsoft Bing Map and HERE Map have some limitations as effectively.

For extra data have take a look at the hyperlink under: