SQL Server Microsoft Excel – Power Query

SQL Server Microsoft Excel –  Power Query formerly known as “Data Explorer”, when it was launched for community preview, is an MS Excel Add-in to enhance self-service BI and Analytics user experience using Excel as a data platform. The add-in can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=39379.

In this post, I’ll demonstrate a use case for Power Query which can be used for data modelling and structuring the data for quick analysis.

Once you have downloaded the add-in and installed the executable, you can access Power Query from the ribbon shown here:

1_Microsoft_Excel_Power_Query

In case after successful installation you do not see this option, you can manually enable the Power Query option by navigating to Options à Add-ins à Under Manage Drop Down select COM Add-ins à Check the Microsoft Power Query for Excel and click on OK.

Here let’s create a data model and later a Power View report using Wikipedia page as a source. For this select the data source as Web.

2_Microsoft_Excel_Power_Query

This will open a dialog box where you can enter the URL of any page which has “tables”. Here I am using the URL  for my data model.

3_Microsoft_Excel_Power_Query

Click on OK and this will open the Power Query editor console.

4_Microsoft_Excel_Power_Query

On the left is the list of all tables which were found while the page was scanned. In the middle you can see the familiar formula bar which highlights the current URL under study. On the left (not shown in the image) is a horizontal bar which will list all actions or steps taken while modelling the data. The steps can be rolled back at any stage as the metadata is persisted all along the session. There is a button “Done” which will transpose the model back to Excel once the design is completed for further analysis. Click on the option States of India and the data pane will pop up the associated data found on the page.

5_Microsoft_Excel_Power_Query

Similarly, you can select other tables like Union Territories and the associated data will be displayed on the data pane.

   

Now we have a raw data available, let’s model the data with only necessary columns in necessary format for the reporting purpose. At this stage, you can add or remove columns. To remove column(s) from the model, select a column right click and click on Remove Column option. Also, to keep the selected column list and remove all others, there is an option called as Remove other columns, as shown below

6_Microsoft_Excel_Power_Query

This action will drop all columns except the ones highlighted. The resultant action will produce the following schema. The horizontal bar you see on the right is the Steps bar which I’d discussed earlier. This is a list of all actions which has taken place on the original data.

7_Microsoft_Excel_Power_Query

This is the final outlay of the data model. You can apply several operations like renaming a column, removing duplicate columns, grouping the data and applying aggregate functions like SUM, MIN, MAX etc. on the column data along with the grouped column. Once you click on Done, the data model is exported to a new Excel sheet.

8_Microsoft_Excel_Power_Query

Now as the data model is ready, it can be used to create reports for analysis using standard pivots or charts or using Power View reports or even Power Maps. You can also add this model to Power Pivot as a table by click on the option Load to data model (not shown in the image) under Query Settings.

Let’s create a Power View reports using this data. Ensure that you have downloaded and installed Power View for Excel and the add-in is enabled. Click on Power View from the Insert Tab and select Power View.

9_Microsoft_Excel_Power_Query

10_Microsoft_Excel_Power_Query

 

The idea presented here is that no longer the data sources for reporting and presentation purposes be structured a set. Even unstructured sources like web logs and web pages now can be  mined for analytics. This is one such example presented using a web page as a source.

This is the first in series of blogs for PowerBI. Tune in for more posts next week. Till that time, Happy learning

 

Regards

Raunak Jhawar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

Leave a Reply

Your email address will not be published.