Tutorials

Power BI

It is very easy to use Geolake in Power BI to geocode addresses and expand your dataset with geographical information.

For this example, we will start with an Excel sheet that contains some sample store locations, with their address and sales totals from last year.

Before we get started, make sure you have a Geolake account. Log in and in your dashboard, look at the “API KEYS” section and click “Add New”. Give your API Key a name and click on “Create Key”. In the API Keys table, you will now see your newly created API Key. This will be a random alphanumerical value. Take note of this value, as we will need it later.

In Power BI - click on “Import data from Excel” and select your file to get started. Check the box for the sheet you want to import and click “Transform Data” to get started.

The Power Query Editor will now open up in a new window. In the top bar, click “New Source” and choose “Blank Query” all the way at the bottom.

A new query called “Query1” will now be added on the left. Click on this query and then rename the query in the panel on the right to something like “GeolakeGeocode”:

In the list of queries on the left, now right click on GeolakeGeocode and choose “Advanced Editor”.

A new “Advanced Editor” window will pop up, with a text area for custom code. In here, remove the code that is there and replace it with the following code:

let
    GeolakeLookup = (address as text) =>
let
    Source = Json.Document(Web.Contents("https://api.geolake.com/geocode?api_key=YOUR_GEOLAKE_API_KEY&q=" & address)),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded place" = Table.ExpandRecordColumn(#"Converted to Table", "place", {"countryCode", "countryName", "currencyCode", "timezone", "tld", "city", "languages", "zipcode", "congrDistrict"}, {"place.countryCode", "place.countryName", "place.currencyCode", "place.timezone", "place.tld", "place.city", "place.languages", "place.zipcode", "place.congrDistrict"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded place",{{"success", type logical}, {"latitude", type number}, {"longitude", type number}, {"accuracyType", type text}, {"place.countryCode", type text}, {"place.countryName", type text}, {"place.currencyCode", type text}, {"place.timezone", type text}, {"place.tld", type text}, {"place.city", type text}, {"place.languages", type text}, {"place.zipcode", type text}, {"place.congrDistrict", type text}})
in
    #"Changed Type"
in
    GeolakeLookup

Make sure to replace “YOUR_GEOLAKE_API_KEY” with your actual Geolake API Key. Click “Done” to save this custom code:

Now click on “Sheet1” in the left list of queries again, go to the “Add Column” top menu and click on “Invoke Custom Function”

In the popup that opens, pick “GeolakeGeocode” as the “Function query”, then for the “address” parameter, select the column from your sheet that contains the full address information, then click “OK” to proceed.

If you get a warning about data privacy, click on “Continue”, then check the box “Ignore Privacy Levels for this file”. This will allow Power BI to send the addresses to Geolake for geocoding and click “Save” in this dialog.

Depending on your dataset size and your Geolake subscription level, this may take some time. Once this is done, you will have a new column that contains geocoding information in a nested table format. To add the relevant columns, click on the little icon on the right of the GeolakeGeocode header:

You could for instance select “latitude”, “longitude” and “place.timezone”, then click “OK” to add these columns:

Your table will now look like this:

In the top left of the Power Query Editor, click on File, then “Close & Apply”:

Power BI will now load call the Geolake API to geocode all of your addresses, this may take some time again based on the size of your table and your Geolake subscription level.

To use your new data in a visualization, you could for instance add a ‘Map’ visualization. In the right panel “Fields”, drag your new “GeolakeGeocode.latitude” field into the “Latitude” visualization property and same use “GeolakeGeocode.longitude” for “Longitude”. For the “Bubble size”, we will use the “Annual Sales” field from our dummy data.

We will now get a visualization such as this:

Address components

In the previous example, we used a full address that was stored in a single column. If your dataset contains separate columns for address, city, zipcode, etc, you can also use a different function to send these fields separately. This can help remove any ambiguity. To use this, simply replace the custom code for your query with:

let
    GeolakeLookup = (optional address as text, optional city as text, optional zipcode as text, optional state as text, optional country as text) =>
let
    Source = Json.Document(Web.Contents("https://api.geolake.com/geocode?api_key=YOUR_GEOLAKE_API_KEY&address=" & (if address is null then "" else address)
    & "&city=" & (if city is null then "" else city)
    & "&zipcode=" & (if zipcode is null then "" else zipcode)
    & "&state=" & (if state is null then "" else state)
    & "&country=" & (if country is null then "" else country))),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded place" = Table.ExpandRecordColumn(#"Converted to Table", "place", {"countryCode", "countryName", "currencyCode", "timezone", "tld", "city", "languages", "zipcode", "congrDistrict"}, {"place.countryCode", "place.countryName", "place.currencyCode", "place.timezone", "place.tld", "place.city", "place.languages", "place.zipcode", "place.congrDistrict"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded place",{{"success", type logical}, {"latitude", type number}, {"longitude", type number}, {"accuracyType", type text}, {"place.countryCode", type text}, {"place.countryName", type text}, {"place.currencyCode", type text}, {"place.timezone", type text}, {"place.tld", type text}, {"place.city", type text}, {"place.languages", type text}, {"place.zipcode", type text}, {"place.congrDistrict", type text}})
in
    #"Changed Type"
in
    GeolakeLookup

If you are using this approach, then you’ll have to pick columns to map to your address, city, zipcode, state and country in the “Invoke Custom Function” step. All of these are optional, so you can also use this to simply geocode by zipcode for instance. Make sure that all columns are of the type ‘text’, this is especially important for the ‘Zipcode’ column. If your zipcode column is a number type, change it to Text by clicking on the icon on the left of the column header and replacing the type with ‘Text’