Using Excel 2013's Power Query and Power Map: Part of Excel's Power BI Suite

Using Excel 2013's Power Query and Power Map: Part of Excel's Power BI Suite
Page content

Installing the Power BI Suite

Once you have Excel 2013 Professional Plus installed, you will need to download and run separate installers for Power Query and Power Map. The Power Query setup can be found here. Likewise, Power Map is available here.

Power Query

Power Query allows you to connect data from different sources but instead of only allowing you to connect to internal resources, you can merge data from both online (including public) locations and private locations such as your work systems.

Let’s say we want to report on the number of electric vehicles sold in the US. To get started we launch Excel. Click on the Power Query tab and then click Online Search. In the search tab we type in something like electric car sales. In the results pane we can see a number of public data sources. If we hover over a source we can see the data Excel would import (Figure 1). Once we find a data set we wish to import we click it to import it into Excel. If we hover over the data set before importing it we can also determine which columns of data get imported and even filter out results. 

Once the data has been imported we have a Query Settings section available on the page. This allows us to manually refresh the data and we also have the ability to filter the data by using the Filter & Shape button (Figure 2). 

We can also perform two queries and merge them together. If we run through and perform another online query – this time for electric car sales in Germany and add it to our Excel sheet (Figure 3) we can then click on the Merge button under the Power Query menu. 

From the Merge window we select our two queries and then click on the column that will be merged – in this case we want to view all models (Figure 4). Once we click OK we will see the query editor which will allow us to filter out data we don’t need to keep. Click Done and you’ll be taken to your new worksheet with the merged results (Figure 5). 

Many other sources can be queried and mashed together – this just touches the tip of what you can do with Power Query. 

Power Map

Power Map is another new feature in the Power BI suite of tools that lets you visualize data against a geographical map. Using a set of sample data from Microsoft you can see some pretty amazing maps (Figure 6). This figure shows the age of houses in Dallas Texas and the amount of energy used by them. 

Let’s do a simple Power Map to show just how easy it is. I did a quick Power Query for the number of Law Enforcement employees by state from this URL (Figure 7). Click on the Insert menu and then Map. After a minute a map should pop up and start populating small blue squares on each state. This first screen will ask you to confirm the geo-location field. In our case, we are just using state names. Other geo-location data can be used such as addresses, regions, zip codes, and latitude and longitude. 

Click the next button at the bottom. We can now decide what the blue square represents. We can click on the various fields to build our interactive map. In Figure 8, I’m displaying the sum of all male and female employees as a simple bar chart for each state. It’s now incredibly easy to see which states have the most (and fewest) law enforcement employees. 

You can also add different layers and charts to the map. Clicking the 2D chart button will show a simple 2D chart overlaid on the map. If you want to create a movie you can click the Create Movie button to create a virtual walkthrough of the data.

This was just a brief overview of using Power Query and Power Map but even though these two tools are still in Preview they are very useful for finding, merging and visualizing your Excel data.