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

In this article I show you how to get all of these information out of Google Maps by passing existing coordinates to Google Maps geocoding API.

Requirements

To be able to follow this post steps you need to:

  • Have a Google account
  • Activate the Google Maps Geocoding API
  • Find a desired API in Google Developers Guide
  • Use the API in Power BI
  • A GPS device data exported to CSV file

Activate the Google Maps Geocoding API

I assume you already have an existing Google account or you know how to create a new one, so I jump straight into the next step which is activating geocoding API. To be able to start using the Google Maps Geocoding API you need to get an API key first.

Google Maps Geocoding API

  • Scroll down and click “Get A Key”

Get Google Maps API Key

  • This will navigate you to Google Developers Console
  • Click “Yes” then click “Agree and continue”

Get Google Maps API Key 2

  • Type in a name then click “Create”
  • Get Google Maps API Key 3

You successfully created an API key. We will use this key in the next sections of this article.

Using Google Maps Geocoding APIs In Power BI

Now that we have an API key we can use lots of Google Geocoding APIs available here. As you can see there are lots of useful APIs you can use to leverage your Power BI model. In my sample I need to retrieve Country, Post Code and Street Address out of existing coordinates generated by my bike GPS computer. So I use Reverse Geocoding (Address Lookup). Here is a sample:

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

If you copy and paste the above API in your web browser you can see its output which is a JSON providing detailed information about the location.

Note: Do not forget to copy and paste your API key at the end of the API.

The idea is to define a function in Power BI then pass latitude and longitude to the function to get Country, Postal Code and Street Address.

How it works

  • Open Power BI Desktop
  • Get Data from Web
  • Paste the API in the URL section then click OK

Power BI Desktop Get Data From Web

  • Stick with the default and click “Connect”

Power BI Desktop Access Web Content

  • This opens “Query Editor”
  • Click “List”

Power BI Desktop Query Editor Expand List

  • Convert the list to table by clicking “To Table” button from “Transform” tab from the ribbon

Power BI Desktop Convert List to Table

  • Click on expand complex column icon (image) to expand the table then tick “formatted_address” and “types” columns then click OK

Power BI Desktop Expand Complex Column

Power BI Desktop Expand Complex Column 2

  • Expand the “types” column as well

Power BI Desktop Query Editor

  • Filter “types” column to show “Country”, “Postal Code” and “Steer Address”

Power BI Desktop Query Editor Filter Column

Power BI Desktop Query Editor 2

  • We need to the columns as rows so we need to transpose the table. So go to “transform” tab and click “Transpose”

Power BI Desktop Transpose Table

  • As you see the second row contains the column names. Click “Reverse Rows” to show the second row first

Power BI Desktop Reverse Rows

  • Click “Use First Row As Headers”

Power BI Desktop Use First Row As Header

  • Rename the query then save the model

Power BI Desktop Rename Query

Export Power BI Service Data to SQL Server

In this post I explain how to export Power BI Service data to SQL server. YES! You can export data from Power BI service to a SQL Server database sitting in your on-premises environment. Keep reading to see how.

How It Works?

This is going to be a short post as I already covered the first part of the process in my other post on Export Power BI Data to SQL Server with R. So in this post I show you how to use the Power BI Desktop file you already created using the method explained in that blog post to export your Power BI Service data to an on-premises instance of SQL Server. All you need to do is to

  • Publish the existing Power BI Desktop solution to Power BI Service
  • Install On-premises Data Gateway in PERSONAL MODE

Note: R is NOT supported by the current version (Version Number: 14.16.6614.5) of the On-premises Data Gateway in Enterprise Mode.

After you successfully published the model to Power BI Service you’ll notice that you cannot refresh the model if you don’t install the On-premises Data Gateway in Personal Mode.

To see the dataset settings:

  • Login to Power BI Service
  • Click the gear icon (image)on top right of the page
  • Click “Settings”

image

  • Click “Datasets” tab then click on the desired dataset
  • Expand “Gateway connection”

image

You can download On-premises Data Gateway from here. The installation is super easy you just have to keep in mind to

  • Select “Personal Mode” when you’re installing the gateway.

image

  • Now go back to the “Dataset settings” and expand “Gateway connection”. The Gateway should be online on Personal Mode.
  • Tick the “Use your data gateway (personal mode)

image

  • Expand “Data source credentials” and click “Edit Credentials” for all data sources and pass the credentials accordingly
  • To test the solution just navigate to your workspace and refresh the dataset
  • At this point you’ll get a warning message as below. Click OK

image

  • Now query the underlying table(s) in SQL Server database to see if the data has been imported successfully
  • Expand “Schedule refresh” and change the configuration as desired the click “Apply”

image

All done!

 

http://biinsight.com/export-power-bi-service-data-to-sql-server/

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/

Beyond Cryptocurrencies: 6 Use Cases of Blockchain Technology You Need to Know

Applications of Blockchain

  1. Identity

Let me start with identity cards, badges, and things. Fancy designs can be replicated. You know about 3D printers right? How do you verify them? Some organisations provide online verification service. You can scan the card or punch in the number and verify if it’s authentic. But, this can be costly, you need to pay for the cloud. With Blockchain you don’t have to build your own identity infrastructure, you can use Ethereum’s open Blockchain to store the identity details. Anyone who wants to verify just has to query the open Blockchain.

2. Notary

Most of your ownership records are stored in paper ledgers. These can be tampered. The data on Blockchain cannot be altered. On a Blockchain there are two things. A block and a chain. At a very high level, it is just a chain of blocks. Since it’s inside computers, we can rule out the physical stuff. Here digital information is divided into blocks and linked together. For example consider the following blocks, each represent a country. Each of them contains the city names of the respective country.

Wait, there is something more. Each of these blocks has something called a hash. A hash is a set of characters (eg. “1hi515AHA5H” ). Hash is derived from the information contained in the block. The block of U.S.A has cities New York, Los Angeles, and Chicago. So the hash would be something like “NYLAC” (Technically that’s not the case, but you get the idea).

Every successive block will contain the previous block’s hash. This is what binds them together (The force). If someone tampers the first block to add the city Boston. The new hash becomes “NYLACB”. However, the succeeding block of India has already stored the hash as “NYLAC”. This mismatch will break the chain. So the purpose of hash is to make sure no one tampers it. The point I am trying to say is you can’t tamper my records. If you do, I will know.

3.Digital Assets

I hope you have heard about ICOs(Checkout TravelChain). It’s a new way to raise investment. Anyone anywhere can now become an investor. When someone invests in your company you should give them something back right? Like stocks or shares? ICOs offer a digital asset called token. These tokens will be stored on your Blockchain wallet (Checkout Coinbase). You will be able to use these tokens to pay for the company’s services or even exchange them at a later date when its value increases.

4. Smart Contracts

Credits : xkcd

Mark and Sara

Mark hasn’t paid his rent for five months. When Sara questions he promise to pay later. She is helpless. She can’t afford a lawyer. Courts take eight months to almost a year to enforce action. The only option is to persuade Mark.

Joe’s business

Joe is a businessman. He does business with different corporates on a frequent basis. A few months ago he signed a contract with a retailer. Though the conditions of the contract were met. The retailer refused to pay. These people take advantage of the legal system and persuade Joe to settle for less pay. Joe had such experiences before. In some cases, he went to court. The time and money he spent there cost him his profits.

How do we help Sara and Joe?

Have we solved this problem elsewhere? In Sara’s case, we need to make Mark pay the rent every month. A time-based trigger. Your calendar app uses such trigger to give you notifications of predefined events.

In Joe’s case, once terms of the agreement is met the party needs to pay. It’s a condition based trigger. Consider the last time you purchased an ebook from Amazon. Amazon will only deliver it once the payment is confirmed.

The point is computer programs execute such instructions consistently. It did when you clicked on this article, scrolled down, etc . In order to help Sara, we need to convert the agreements of the contract into code.

Pseudo code of the smart contract between Sara and Mark

If today’s date is 30th and rent is not paid then
Transfer $500 from Mark’s account to Sara’s account

But where do we deploy this code? It should be deployed on computers of all parties involved. Sara’s and Mark’s bank will be part of a private Blockchain network. Joe and Sara will sign a coded contract(a.k.a smart contract). Then it’s deployed on the network. Both Mark’s and Sara’s bank will have a copy. On 30th of every month when the clock ticks 12.00. The agreed amount gets transferred from Mark’s account to Sara’s account. Joe started using smart contracts to enforce his clients to pay the agreed amount.

Sara is happy because she doesn’t have to trust Mark’s consent to transfer rent. Joe’s glad because he doesn’t have to go to a court for justice. Instead, he can spend those efforts to grow his business.

5. Digital Voting

Remember the last time you stood in a queue to vote? What if you can do it at home? The biggest problem with online voting is its security. Votes can be tampered or hackers can find out who you voted for. Blockchain can make your vote anonymous and provide better security. Since voter turnout in America is low, digital voting can bring in more participants.

6. Distributed Storage

Presently you use services like Gdrive, Dropbox to store your files. The problem is you have to trust them that they won’t peep into your data. Governments can force them to disclose data if required. On Blockchain data is decentralised its stored on different computers on the network with high encryption. This can reduce costs. If you have excess storage on your computer, you can rent your excess storage capacity. Storj is an example. Yea, Airbnb or an Uber for digital storage.

 

https://hackernoon.com/popular-use-cases-of-blockchain-technology-you-need-to-know-df4e1905d373

More interesting readings:

 

INDEX MATCH in Excel

The INDEX MATCH MATCH formula is the combination of two functions in Excel: INDEX and MATCH.

=INDEX() returns the value of a cell in a table based on the column and row number.

=MATCH() returns the position of a cell in a row or column.

Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria.  For short, this is referred to as just the Index Match function.

#1 How to use the INDEX formula

Below is a table showing people’s name, height and weight. We want to use the INDEX formula to look up Kevin’s height… here is an example of how to do it.

Follow these steps:

  1. Type “=INDEX(” and select the area of the table then add a comma
  2. Type the row number for Kevin, which is “4” and add a comma
  3. Type the column number for Height, which is “2” and close the bracket
  4. The result is “5.8”

Index formula Excel

 

#2 How to use the MATCH formula

Sticking with the same example as above, let’s use MATCH to figure out what row Kevin is in.

Follow these steps:

  1. Type “=MATCH(” and link to the cell containing “Kevin”… the name we want to look up
  2. Select the all the cells in the Name column (including the “Name” header)
  3. Type zero “0” for an exact match
  4. The result is that Kevin is in row “4”

Match formula Excel

Use MATCH again to figure out what column Height is in.

Follow these steps:

  1. Type “=MATCH(” and link to the cell containing “Height”… the criteria we want to look up
  2. Select the all the cells across the top row of the table
  3. Type zero “0” for an exact match
  4. The result is that Height is in column “2”

Match function

 

#3 How to combine INDEX and MATCH

Now we can take the two MATCH formulas and use them to replace the “4” and the “2” in the original INDEX formula. The result is an INDEX MATCH MATCH formula.

Follow these steps:

  1. Cut the MATCH formula for Kevin and replace the “4” with it
  2. Cut the MATCH formula for Height and replace the “2” with it
  3. The result is Kevin’s Height is “5.8”
  4. Congratulations, you now have a dynamic INDEX MATCH formula!

Index Match Match in Excel

 

https://corporatefinanceinstitute.com/resources/excel/study/index-match-formula-excel/