PowerBI: How to Get Address by Passing Existing Coordinates to Google Maps Reverse Geocoding API

Create a Function from an Existing Query (Address Lookup)

From the previous tutorial, we loaded the geo-location information for just one coordinate. Now we need to turn the query to a function so that we can invoke it for all coordinates we have in our dataset.

A function construction is as below:

  • Click “Advanced Editor”

Power BI Desktop Advanced Editor

  • Change the code as per the screenshot below ===> “Sample Data single load”
let
  get_geo = ( lat as text, lng as text) =>
let
 Source = Json.Document(Web.Contents
("https://maps.googleapis.com/maps/api/geocode/json?latlng="&lat&","&lng&"
&ampAIzaSyBwKQ7VSYAw4zE0zTtMCQ-k839qoeNdeso")),
 results = Source[results],
 #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"formatted_address", "types"}, {"Column1.formatted_address", "Column1.types"}),
 #"Expanded Column1.types" = Table.ExpandListColumn(#"Expanded Column1", "Column1.types"),
 #"Filtered Rows" = Table.SelectRows(#"Expanded Column1.types", each ([Column1.types] = "postal_code" or [Column1.types] = "street_address")),
 #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
 #"Reversed Rows" = Table.ReverseRows(#"Transposed Table"),
 #"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]),
 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"street_address", type text}, {"postal_code", type text}})
in
 #"Changed Type"
in
  get_geo

Power BI Desktop Create Function

1– Function internal name

2 & 4 – Input parameters

3 & 5 – Parameters’ data types

6 – Function body

7– Output

Note: The name that will be used to revoke the function is the query name which is “fn_GeoLocation” in our sample. —> “Sample Data single load”

  • Continue modifying the codes as below then click “Done”

Power BI Desktop Function Parameters

  • What we have done above is that we replaced the static latitude and longitude with parameters. We used “&” to concatenate both sides of the source.
  • As soon as you click “Done” Power BI detects the query as a function

Power BI Desktop Invoke Function

  • To test the function just click “Invoke” and enter latitude and longitude then click OK

Power BI Desktop Invoke Function 2

Power BI Desktop Invoke Function 3

  • Delete the invoked function step

We successfully defined a query as a function so that we can invoke it anytime.

The next step is to load GPS data from CSV. I’m using my bike computer data that I exported to CSV format earlier.

Download sample CSV file here.

  • In Query Editor, click “New Source” from “Home” tab then click “CSV”

Power BI CSV

  • Browse and open the CSV file
  • Click OK

Power BI Query Editor CSV

  • Click “Add Custom Column” from “Add Column” tab and name it “Geolocation”
  • Enter the following formula to invoke the fn_GeoLocation function and click OK
  • “Sample Data single load”

Power BI Query Editor Add New Column

Note: I used “Number.ToText” function to convert latitude and longitude data type from number to text as we defined the function parameters as text.

  • You’ll get a “Information is required about data privacy” warning message. Click “Continue”

Power BI Query Editor Add New Column 2

  • Select a desired privacy level from the list then click “Save”

Power BI Privacy Levels

Note: Learn more about “Privacy levels” here.

  • Scroll right to find the new added column
  • Expand “Geolocation” column

Power BI Expand Complex Column 2

Voila! We have got all information needed

  • Click “Close & Apply” from “Home” tab from the ribbon and wait until the data loads into the model.

Power BI Query Editor Close & Apply

Note: It might take sometime to load data into the model based on your dataset size. This is because of sending coordinates to Google Maps row by row then loading the results into the model, so the process could be slow if you have a large dataset. There are also some limitations applied to the API usage.

http://biinsight.com/power-bi-and-google-maps-api-address-lookup/

Power BI Use Case: Energy Industry

This report focuses on demand forecasting within the energy sector. The views provide region level energy usage status and forecast of future usage for optimizing the operations.

Case Study:

Storing energy is not cost-effective, so utilities and power generators need to forecast future power consumption so that they can efficiently balance the supply with the demand. During peak hours, short supply can result in power outages. Conversely, too much supply can result in waste of resources. Advanced demand forecasting techniques detail hourly demand and peak hours for a particular day, allowing an energy provider to optimize the power generation process. This report focuses on demand forecasting within the energy sector. The report provides region level energy usage status and forecast of future usage for optimizing the operations.


The ‘Energy Solution Forecast’ page shows the demand forecast results from Azure Machine Learning model and different error metrics for user to identify the quality of the model. Temperature and its forecasts are used as a feature in the machine learning model.

energy2


The ‘Energy Solution Status Summary’ page shows the overall status of energy demand of each region. User can select a single region by clicking the filter on the left to investigate each region’s status.

energy

Useful Power BI Visuals Reference

How to choose the right visuals for your reports/dashboards among all these resources?
Here is a  clean reference that lists nearly all the visuals available today, categorized by their case of use. The categories we identified are:

  1. Comparison
  2. Change over time (Trend)
  3. Part-to-whole
  4. Flow
  5. Ranking
  6. Spatial
  7. Distribution
  8. Single

4 Coolest Power BI Tutorial Sites For Beginners

1. Power BI Guided Learning

This is the Power BI official channel. When following this one, you will find many Power BI tutorial videos as well as documentations. You can also use the sample database like AdventureWorks in Power BI desktop then do the trial and error using that database in all visuals. Moreover, you can learn about the tools and other available options in PowerBI desktop through the website. Here’s a protip: work your data on excel before using PowerBI – you’ll save a lot of time.

1


2. Microsoft Virtual Academy

This site provides hundreds of free courses which are designed for Developers, IT Pros, Data Pros, and Students. The topic is diverse, from Cloud Development to Visual Studio to Mobile Development, and more. This site is an excellent way to stay up to date on the latest Microsoft technologies which include Power BI tutorial.  The new course is updated weekly!

2


3. Analyzing and Visualizing Data with Power BI

This course is a part of the Microsoft Professional Program Certificate in Big Data and the Microsoft Professional Program Certificate in Data Science. It will walk you through Power BI, from the basic knowledge i.e how to connect to and import your data, to the advanced level i.e. how to author reports using Power BI Desktop and publish those reports to the Power BI service. The series of short, lecture-based videos are accompanied by demos, quizzes, and hands-on labs to test your knowledge. This free EDX online course is definitely a great resource for learning Power BI.

3


4. Microsoft Technet 

On this website, you can find everything related to Power BI such as key resources, tutorials, case studies and many more. Technet forum is also the best place for you to exchange ideas with Power BI users and gain more knowledge from many Microsoft experts.

4

 

http://niftit.com/get-power-bi-tutorial/