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.

Power BI Map using Coordinates

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

Power BI Map using Location

This may be achieved from Question Editor in M language.

Creating Location from Latitude and Longitude in Power BI

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.

Google Maps Geocoding API

  • Scroll down and click on “Get A Key”

Get Google Maps API Key

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

Get Google Maps API Key 2

  • Sort in a reputation then click on “Create”
  • Get Google Maps API Key 3

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.

JSON Output of Google Reverse Geocoding 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

Power BI Desktop Get Data From Web

  • Stick to the default and click on “Join”

Power BI Desktop Access Web Content

  • This opens “Question Editor”
  • Click on “Record”

Power BI Desktop Query Editor Expand List

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

Power BI Desktop Convert List to Table

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

Power BI Desktop Expand Complex Column

Power BI Desktop Expand Complex Column 2

  • Broaden the “varieties” column as effectively

Power BI Desktop Query Editor

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

Power BI Desktop Query Editor Filter Column

Power BI Desktop Query Editor 2

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

Power BI Desktop Transpose Table

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

Power BI Desktop Reverse Rows

  • Click on “Use First Row As Headers”

Power BI Desktop Use First Row As Header

  • Rename the question then save the mannequin

Power BI Desktop Rename Query

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”

Power BI Desktop Advanced Editor

  • Change the code as per screenshot under

Power BI Desktop Create Function

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”

Power BI Desktop Function Parameters

  • 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

Power BI Desktop Invoke Function

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

Power BI Desktop Invoke Function 2

Power BI Desktop Invoke Function 3

  • 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”

Power BI CSV

  • Browse and open the CSV file
  • Click on OK

Power BI Query Editor CSV

  • 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

Power BI Query Editor Add New Column

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”

Power BI Query Editor Add New Column 2

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

Power BI Privacy Levels

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

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

Power BI Expand Complex Column 2

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.

Power BI Query Editor Close & Apply

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.

Power BI and Google Maps API

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:



Supply hyperlink

Leave a Reply

Your email address will not be published. Required fields are marked *