I was tipped off about a new add-on for Excel 2013 that Microsoft have just released called Power Query, so I had to take a look at it. As the name hints, this is the data query function on steroids, and it looks great.

The following walkthrough is an example of what I did.

A Quick Tour of Power Query

Power Query is an extended version of the Data Query function that you may have used in Excel in the past, except the data sources that it is able to query so much better.

In this section we will look at what the Power Query Add-In provides us.

Getting Ready…

The first step in the process is to download Power Query.

Power Query is easy to find. I just did a Bing search and found it right away on the Microsoft Download Center:

http://www.microsoft.com/en-us/download/details.aspx?id=39379

How It Works…

When you install Power Query, you will notice a new ribbon bar has been added.

You can query from files… especially interesting here is the From folder option that allows you to query the file system and return back file information.

You can query other databases… including Oracle, DB2, MySQL, PostgreSQL and Teradata databases.

And you can query from other sources… including SharePoint Lists, Active Directory, Big Data datasources, and Facebook.

All of these additional data sources can be queried and combined into data mashups… how cool is that.

Using Power Query to Query your Facebook Friends

Now that we have Power Query installed, it’s time to start using it to grab some of the data.

In this example I will show how you can use Power Query to query your Facebook account.

How to do it…

To query Facebook using Power Query, follow these steps:

1. Select the POWER QUERY ribbon bar within Excel and select the From Facebook option from the From Other Sources menu button dropdown.
   
2. This will open up the Facebook options form. If you want to mine information on another user, then you can type in their username, or in this case, use me to access your own personal data.
   
3. In the Connection name you can select the type of information that you want to query from Facebook. In this case I want to find all of my friends.
   
4. Once you have selected the source and type of data that you want to query then click the OK button.
   
5. Since this was the first time that I queried Facebook, it is going to ask me to sign in.
   
6. It will ask you for your Facebook credentials.
   
7. Once you have stored your Facebook credentials within Power Query, you won’t have to do this anymore.
   

   Note: if you want to update or remove these credentials, just click on the Data Source Settings button within the Machine Settings group, and you will be able to delete the username and password.

8. Power Query will now return back a subset of the data from Facebook as a test query.
   

   Notice though that some of the data shows up as a green hyperlink like the data in the object_link field. This means that there is more data beneath.

9. Double clicking on the link will drill down into that data.
   
10. You can keep on drilling down into the record to navigate through the data.
    
11. If you want to expand out any of the data at the lower levels and include it in your query, then click on the expansion icon in the top right of the field name header, and you will be able to see all of the available sub-fields.
    

    Select the fields that you want to include and click OK.

12. Now your query will start to flatten out and you can use the data.
    
13. Once you have finished manipulating the query, click the Done button.

How it Works…

The query will now be available within Excel as a worksheet.

Using Power View to Analyze your Facebook Friends

Once you have queried Facebook using Power Query, you will probably want to visualize it. The best tool for that is Power View.

In this example I will show how to use Power View to quickly create a dashboard analysis.

How to do it…

To analyze your Facebook friends with Power View, follow these steps:

1. Select the query data within Excel and then click on the Power View button within the Reports group of the Insert ribbon bar.
   
2. This will open up the blank Power View canvas with all of the query fields available within the field explorer.
   
3. The first query that I will create is a simple analysis of my friends. In this case the gender breakdown. To do this I just selected the object_link_gender field and added it to my reporting canvas.
   
4. Then I converted the Table into a Matrix by selecting Matrix from the Table button on the DESIGN ribbon bar.
   
5. To count the records I then added the object_link_gender field to the VALUES group in the report designer panel.
   
6. Since this element is a Matrix, I can now change it to a chart. From the Other Chart options in the DESIGN ribbon bar, select the Pie option.
   

How it Works…

After adding a title, and another card view to the dashboard I have a quick summary of my Facebook friends.

Using Power Query to Analyze your Facebook Feeds

Analyzing your friends on Facebook is nice, but something that is even more interesting is to analyze the Facebook Feeds. Power Query allows you to download your complete Facebook feed history so that you can take a walk down memory lane.

In this example I will show how you can use Power Query to view you Facebook Feed activity.

How to do it…

To analyze your Facebook feeds with Power Query, follow these steps:

1. Welect the POWER QUERY ribbon bar within Excel and select the From Facebook option from the From Other Sources menu button dropdown.
2. In the Connection name select the Feed option.
   
3. The Feed query from Facebook returns a little more information than the Friends query…
   
4. But we need a little more for this analysis. Expand out the object_link field.
   
5. This will give us the name of person that is posting to our timeline
   
6. Expand out the status_type field.
   
7. Expand out the object_id field.
   
8. And finally, expand out the shares field.
   
9. After manipulating the query, click on the Done button to transfer it to the Excel workbook.
   
10. Now our query is almost ready to use. The only problem is that I want to analyze this data by the date, and the create_time field is a little too granular. So add a new column to the query for the Year, and use a little bit of string manipulation to extract the year from the create_time field. (Normally I would use the Year() function, but it doesn’t like the Facebook format of the date/time).
    
11. Add another field for the Month.
    
12. And finally, add a Period column that combines the year and the month.
    

How it Works…

Select the query and click on the Power View button within the Reports group of the INSERT ribbon bar.

Create a Matrix Table to analyze the activity by year.

I didn’t really use Facebook that much in 2007 & 2008 I guess…

Create a more detailed matrix field by combining the Year and the month.

Then convert the table to a Bar Chart. This allows me to see where the peaks by month are for my feed posts – October was the obvious winner in general because that’s my birthday month, although February of 2009 must have been a power friending month…

Additionally, add a line graph to the dashboard, using the Period field that you created to show the history over time.

Very cool.

Analyzing Your Facebook Friending History

Something else that I thought would be interesting to analyze is the actual Feed content from Facebook. Since the query returns the activity header, why not analyze the type of feed.

In this example I will show how to analyze the feeds to track friending activity.

How to do it…

To analyze your Facebook Friending, follow these steps:

1. Add another column to the Feed query and mark anything that finishes with “are now friends.”.
   

How it Works…

Now that we have that additional piece of data we can create a simple Power View dashboard showing the friending activity my month.

Analyzing Feed Activity by Application

Hidden in the Feed query are other details that are interesting to visualize. One of them is the application type.

In this example I will show a simple analysis of the Facebook feed to track the applications.

How it Works…

In this example I created a simple dashboard using Power View. The cool think here is that the icon field shows as an image – Power View automatically recognizes it, and if we add it as a field then we can see all of the posts broken out to source.

Filtering out the data by year shows us what was hot at the time.

Also, by creating a date hierarchy in the report, we can drill down by year down into the monthly periods for more information.

I don’t know why my dog – “Barney Fife” was posting so much in 2012… I need to investigate that more.

Summary

Power Query is a great new tool to take advantage of. Using it to query the less structured data like Facebook is just the starting point. Once you have played around with it a little you may want to try creating data mash-up’s by combining the data from one data source with another.

Give it a go and drop me a note when you create something cool.