All of us conduct surveys, get feedback forms or scoring sheets filled. When data is captured in Excel, we typically use Excel Pivot Tables to analyze it.
But what if the data is captured as a list of textual values like this? Many professional survey applications often dump the data in this type of format.
Here is a very simple but extremely useful way of analyzing such badly captured data.
Continue reading Analyzing badly captured survey data
If you use Excel 2010 and Power Query, there is some good news.
Microsoft just released the new version of Power Query – download and it from here. There are many more features added to this new version… here is a quick review.
Continue reading New version of Power Query released. Update it.
In few minutes you can analyze any public Facebook page data.
Here we see how to analyze the likes and comments – instantly.
You need Excel 2010 or Excel 2013 (Professional Plus edition) or Office which is installed from Office 365.
- Close Excel if it is open
- Download a new, extremely powerful tool Power Query for Excel
- Install the tool
- Open Excel
- You should now see a tab called Power Query
- Open Power Query tab and open the From Other Sources dropdown
- Choose Facebook
- Choose me and Statuses (you can choose other types as well)
Remember that you can type any username or object id which has information accessible to you
- Click Ok
- Sign in to your Facebook account
- Save the credentials
- Now it will open a query result window and extract status update information from FB
- At this stage it shows few results. Later it will fetch all possible results.
- Keep the columns Message, Updated Time, Likes and Comments.
- Remove other columns by right clicking on each column and deleting it
- Now we want the count of likes and comments
- As of now, both show Table.
- Notice the small icon next to the Likes and Comments column headings
- Click on that icon and do the following for Likes and Comments
- Choose Aggregate and Count of ID
- Now the Column shows count
- The date time column contains locale information. We want a simple Date Time column.
- Right click on the Updated Time column and choose Date Time
- On the right side pane, expand the Steps area
- Notice that all the steps you performed are remembered.
- Click the Done button
- Wait for data to be fetched
- Now you can use this data in a Pivot Table or PowerView to analyze the FB data easily
This is a PowerView chart of one FB site
- Here is another one as a PivotChart based upon another public site
Now you can do sophisticated FB analysis without knowing any programming, FQL or Graph API syntax.
Explore Power Query.
It has a very powerful syntax. Yes it is complex but it is extremely powerful.
We will cover more of Power Query, Power View and Power Map in future posts.