Power Query for Excel

In this post, I’ll discuss the prerequisites and how to install and enable Power Query (Data Explorer) and I’ll show you how to use this new feature.

Prerequisites:

  • Requires Microsoft Office 2010 SP1 or Microsoft Excel 2013 32-bit or 64-bit
  • Requires Windows Vista (with .Net 3.5 SP1 or greater), Windows Server 2008 (with .Net 3.5 SP1 or greater), Windows Server 2008 R2, Windows 7 or Windows 8.

Installation:

Download and install the preview from Microsoft Download Center

Once the feature is installed open Excel and go to File, Options, Add-Ins. Select COM Add-Ins and click Go.

SQL Freelancer SQL Server Excel Data Explorer Power Query BI
Check Microsoft “Data Explorer” Preview for Excel and click OK.
SQL Freelancer SQL Server Excel Data Explorer Power Query BI
Once Data Explorer has been enabled, the tab will appear above the Office ribbon.

SQL Freelancer SQL Server Excel Data Explorer Power Query BI

Now that we have Data Explorer installed and enabled we can get to the fun stuff.

To see a detailed list of each element you can visit Microsoft Data Explorer Help

If you click on the Data Explorer tab you will notice Get External Data. Just like Excel and PowerPivot, this is where our data source will come from. There are multiple data sources to choose from including websites, files, databases, Active Directory, and even Facebook. Yes….Facebook.

SQL Freelancer SQL Server Excel Data Explorer Power Query BI

In this tip, I’ll show you examples of two data sources. We’ll pull data from a website and create a map report using PowerView and we’ll pull data from Facebook and make a report using PowerPivot.

Web Data Source

In the first example, I’ll pull table data from the web using Wikipedia. Choose “From Web” from the Get External Data section and use the following URL:http://en.wikipedia.org/wiki/List_of_countries_by_population

SQL Freelancer SQL Server Excel Data Explorer Power Query BI

Click OK

In the Query Editor under Navigator, select Countries. This will display rank, country, population, date, % of world population and source. We can filter columns just like in Excel by selecting the header arrow and choosing the appropriate filter. In this example, we’ll filter Source to only includes records that are an official estimate.

SQL Freelancer SQL Server Excel Data Explorer Power Query BI
Click here to view the rest of this post.

Microsoft’s new 3D data visualization BI product – Power Map

Microsoft has introduced a new BI product that will provide 3D data visualization using Bing Maps. This product is still in beta and is codenamed “GeoFlow” but should be out late 2013 or early 2014. This preview will allow you to plot geographic and temporal data visually, analyze that data in 3D, and create visual tours. It is a really cool product and I can’t wait to use it in a business atmosphere.

In this post, I’ll discuss the prerequisites and how to install “GeoFlow” and I’ll give an introduction on how to use this feature.

Prerequisites:

  • Requires Microsoft Office Professional Plus 2013 or Office 365 ProPlus
  • Supports 32-bit or 64-bit machines
  • Requires Windows 7, Windows 8, or Windows Server 2008R2 (requires .NET Framework 4.0)
  • Requires internet access

Installation

Download and install the preview from Microsoft Download Center (make sure Excel is closed while installing)

Open Excel and “Maps” will appear as a new item in the “Insert” tab of the Excel ribbon:

SQL Freelancer SQL Server Excel GeoFlow Power Map BI
Now that “GeoFlow is installed, we can begin using the new feature.

For this tip, I’m using the AdventureWorks2012 database and the result set from the following query imported into Excel:

  SELECT  
  OrderDate, 
  ShipDate, 
  AddressLine1, 
  City, 
  PostalCode, 
  StateProvinceCode
  FROM [AdventureWorks2012].[Sales].[SalesOrderHeader] soh
  JOIN [AdventureWorks2012].[Person].[Address] ps ON soh.ShipToAddressID = ps.AddressID
  JOIN [AdventureWorks2012].[Person].[StateProvince] psp ON psp.StateProvinceID = ps.StateProvinceID
  WHERE StateProvinceCode = 'CA'

SQL Freelancer SQL Server Excel GeoFlow Power Map BI
Once you have the data in Excel, click on the “Insert” tab on the Office ribbon and click Map. You should now see the “GeoFlow” globe:

SQL Freelancer SQL Server Excel GeoFlow Power Map BI
There are a few tabs you can use right away in the ribbon bar. Under the “Map” item you can change Themes and add Map Labels. I like to enable the Map labels so I can see a more detailed version of the map. You can also zoom in and out and move the map using the arrows and +/- buttons in the bottom right corner of the map.SQL Freelancer SQL Server Excel GeoFlow Power Map BI
On the right side you see the “Layer” pane. This is where you will choose your geography visualizations. For this example Ill select “PostalCode” under the range section and since we know that this column stores zip codes we’ll map it to “Zip” under the Geography section:

SQL Freelancer SQL Server Excel GeoFlow Power Map BI

Once the fields are selected, click Map It.

You might notice that 86 records were updated meaning that there are 86 unique zip codes in California and it placed a marker on each zip code:

SQL Freelancer SQL Server Excel GeoFlow Power Map BI
Click here to view the rest of this post.