- slide 2 of 8
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.
- slide 4 of 8
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).
- slide 6 of 8
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.
- slide 7 of 8
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.
- slide 8 of 8
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.