Best Map Visual So Far – Power BI Integrated With ArcGIS

Power BI, integrated with ArcGIS, delivers the world’s leading visual spatial analytics. ArcGIS Maps for Power BI offers enhanced mapping and analysis capabilities, demographic data, and compelling visualizations.

Online Tutorial

Perform spatial analysis

Spatial analysis leads to greater understanding, by mapping relevant locations and spotting relationships and patterns—ultimately helping you determine the best actions to take.

With ArcGIS you can: create heat maps with a click, aggregate data into clusters, determine drive time, and reveal hidden patterns in your data.

Give context to data

Use ArcGIS to analyze data based on spatial qualities. Identify a location in relation to other critical variables.

Add demographic and reference layers, such as income, population, or weather, included free with ArcGIS.

Be right more often

ArcGIS ensures your locations are mapped correctly every time. When your maps match your data, you get accurate spatial analysis and confidence in your decisions.



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.


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:,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”


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


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.


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


  • 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


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


All done!

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”
  get_geo = ( lat as text, lng as text) =>
 Source = Json.Document(Web.Contents
 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}})
 #"Changed Type"

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.

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.

More interesting readings:



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

UPDATE/ADD/ALTER Column and Table in SQL


The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.


UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

UPDATE Multiple Records

It is the WHERE clause that determines how many records that will be updated.

The following SQL statement will update the contactname to “Juan” for all records where country is “Mexico”:


UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

Update Warning!

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!


UPDATE Customers
SET ContactName='Juan';


To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype;


To change the data type of a column in a table, use the following syntax:

SQL Server / MS Access:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;


WHERE CustomerName='Alfreds Futterkiste';

DELETE FROM table_name
WHERE condition;

Delete All Records

DELETE * FROM table_name;


To delete a column in a table, use the following syntax (notice that some database systems don’t allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name;

Part 2:How to Set Up a Virtual Machine

Setting Up a Virtual Machine

Once you’ve decided on a VM app and gotten it installed, setting up a VM is actually pretty easy. We’re going to run through the basic process in VirtualBox, but most apps handle creating a VM the same way.

Open up your VM app and click the button to create a new virtual machine.

You’ll be guided through the process by a wizard that first asks which OS you’ll be installing. If you type the name of the OS in the “Name” box, the app will most likely automatically select the type and version for the OS. If it doesn’t—or it guesses wrong—select those items yourself from the dropdown menus. When you’re done, click “Next.”

Based on the OS you plan to install, the wizard will preselect some default settings for you, but you can change them over the screens that follow. You’ll be asked how much memory to allocate to the VM. If you want something other than the default, select it here. Otherwise, just click “Next.” And don’t worry, you’ll be able to change this value later if you need to.

The wizard will also create the virtual hard disk file to be used by the VM. Unless you already have a virtual hard disk file you want to use, just select the option to create a new one.

You’ll also be asked whether to create a dynamically allocated or fixed size disk. With a dynamically allocated disk, you’ll set a maximum disk size, but the file will only grow to that size as it needs to. With a fixed size disk, you’ll also set a size, but the file created will be that large from its creation.

We recommend creating fixed size disks because, while they eat up a little more disk space, they also perform better—making your VM feel a bit more responsive. Plus, you’ll know how much disk space you’ve used and won’t get surprised when your VM files start growing.

You’ll then be able to set the size of the virtual disk. You’re free to go with the default setting or change the size to suit your needs. Once you click “Create,” the virtual hard disk is created.

After that, you’re dumped back into the main VM app window, where your new VM should show up. Make sure the installation media you need is available to the machine—usually this involves pointing to an ISO file or real disc through the VM’s settings. You can run your new VM by selecting it and hitting “Start.”

Of course, we’ve just touched on the basics of using VMs here. If you’re interested in more reading, check out some of our other guides:

Part 1:How to Create and Use Virtual Machines

Virtual machines allow you to run an operating system in an app window on your desktop that behaves like a full, separate computer. You can use them play around with different operating systems, run software your main operating system can’t, and try out apps in a safe, sandboxed environment.

There are several good free virtual machine (VM) apps out there, which makes setting up a virtual machine something anybody can do. You’ll need to install a VM app, and have access to installation media for the operating system you want to install.

What’s a Virtual Machine?

A virtual machine app creates a virtualized environment—called, simply enough, a virtual machine—that behaves like a separate computer system, complete with virtual hardware devices. The VM runs as a process in a window on your current operating system. You can boot an operating system installer disc (or live CD) inside the virtual machine, and the operating system will be “tricked” into thinking it’s running on a real computer. It will install and run just as it would on a real, physical machine. Whenever you want to use the operating system, you can open the virtual machine program and use it in a window on your current desktop.

In the VM world, the operating system actually running on your computer is called the host and any operating systems running inside VMs are called guests. It helps keep things from getting too confusing.

In a particular VM, the guest OS is stored on a virtual hard drive—a big, multi-gigabyte file stored on your real hard drive. The VM app presents this file the guest OS as a real hard drive. This means you won’t have to mess around with partitioning or doing anything else complicated with your real hard drive.

Virtualization does add some overhead, so don’t expect them to be as fast as if you had installed the operating system on real hardware. Demanding games or other apps that require serious graphics and CPU power don’t really do so well, so virtual machines aren’t the ideal way to play Windows PC games on Linux or Mac OS X—at least, not unless those games are much older or aren’t graphically demanding.

The limit to how many VMs you can have are really just limited by the amount of hard drive space. Here’s a peek at some of the VMs we use when testing things out while writing articles. As you can see, we’ve got full VMs with several versions of Windows and Ubuntu installed.

You can also run multiple VMs at the same time, but you’ll find yourself somewhat limited by your system resources. Each VM eats up some CPU time, RAM, and other resources.

Why You’d Want to Create a Virtual Machine

Aside from being good geeky fun to play around with, VMs offer a number of serious uses. They allow you to experiment with another OS without having to install it on your physical hardware. For example, they are a great way to mess around with Linux—or a new Linux distribution—and see if it feels right for you. When you’re done playing with an OS, you can just delete the VM.

VMs also provide a way to run another OS’ software. For example, as a Linux or Mac user, you could install Windows in a VM to run Windows apps you might not otherwise have access to. If you want to run a later version of Windows—like Windows 10—but have older apps that only run on XP, you could install Windows XP into a VM.

Another advantage VMs provide is that they are “sandboxed” from the rest of your system. Software inside a VM can’t escape the VM to tamper with the rest of your system. This makes VMs a safe place to test apps—or websites—you don’t trust and see what they do.

For example, when the “Hi, we’re from Windows” scammers came calling, we ran their software in a VM to see what they would actually do—the VM prevented the scammers from accessing our computer’s real operating system and files.

Sandboxing also allows you to run insecure OSes more safely. If you still need Windows XP for older apps, you could run it in a VM where at least the harm of running an old, unsupported OS is mitigated.

Virtual Machine Apps

There are several different virtual machine programs you can choose from:

  • VirtualBox: (Windows, Linux, Mac OS X): VirtualBox is very popular because it’s open-source and completely free. There’s no paid version of VirtualBox, so you don’t have to deal with the usual “upgrade to get more features” upsells and nags. VirtualBox works very well, particularly on Windows and Linux where there’s less competition, making it a good place to start with VMs.
  • VMware Player: (Windows, Linux): VMware has their own line of virtual machine programs. You can use VMware Player on Windows or Linux as a free, basic virtual machine tool. More advanced features—many of which are found in VirtualBox for free—require upgrading to the paid VMware Workstation program. We recommend starting out with VirtualBox, but if it doesn’t work properly you may want to try VMware Player.
  • VMware Fusion: (Mac OS X): Mac users must buy VMware Fusion to use a VMware product, since the free VMware Player isn’t available on a Mac. However, VMware Fusion is more polished.
  • Parallels Desktop: (Mac OS X): Macs also have Parallels Desktop available. Both Parallels Desktop and VMware Fusion for Mac are more polished than the virtual machine programs on other platforms, since they’re marketed to average Mac users who might want to run Windows software.

While VirtualBox works very well on Windows and Linux, Mac users may want to buy a more polished, integrated Parallels Desktop or VMware Fusion program. Windows and Linux tools like VirtualBox and VMware Player tend to be targeted to a geekier audience.

There are many more VM options, of course. Linux includes KVM, an integrated virtualization solution. Professional and Enterprise version of Windows 8 and 10—but not Windows 7—include Microsoft’s Hyper-V, another integrated virtual machine solution. These solutions can work well, but they don’t have the most user-friendly interfaces.

The integration of SSRS and Power BI

The integration of SSRS and Power BI creates a single portal to access all reporting items; which would be from Power BI dashboard.

For using this functionality, some requirements need to be met. You have to use SQL Server 2016 or higher version. Your dataset of the report should be using saved credentials. SSRS report should have charts, gauges, or maps, because only charts, gauges, and maps can be pinned to the dashboard. Power BI Integration in the reporting services configuration manager should be enabled. And finally; SQL Server agent should be up and running because Agent is responsible for keeping that tile up to date.

Using the integration of SSRS and Power BI; you can have tiles in Power BI dashboard which points to SSRS reports for the detailed paginated report. Power BI users will use normal tiles for interactive reports, and they can use SSRS tiles when they want to see the more detailed paginated report in SSRS.