A1: Uninstall Existing version (Yes, it’s not intuitive, but you have to uninstall existing version). Download the version that you want to upgrade to. Install it.
Q2: I’ve SQL 2008 R2 on my machine. Can I install “Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010”?
A2: Yes, go ahead! “SQL server 2012 sp1” in the name is confusing 🙂 It doesn’t matter which version of SQL server you’ve on your machine – In fact, Power Pivot does not need a SQL server to be installed on your machine.
Q3: How do I check Power Pivot version?
A3: Excel > Power Pivot > Settings > You’ll see version # on the top part of the screen.
Data preparation (or call it pre-processing) is an essential and time-consuming part of any data analytic’s project. To that end, I was working on a data set needed some changes before I could plot it on an effective data visualization. Here’s what I did:
My Challenge:
I was working on a data set that looked like this:
Date
Abu Dhabi, United Arab Emirates
Adalaj, Gujarat, India
Addison, TX
1/1/2013
1
4
2
1/2/2013
1
4
2
1/3/2013
1
4
3
1/4/2013
3
3
3
1/5/2013
2
2
4
1/6/2013
2
3
4
1/7/2013
2
3
3
1/8/2013
2
2
4
1/9/2013
2
2
3
BUT: I wanted my data to look like
Date
1/1/2013
1/2/2013
1/3/2013
1/4/2013
1/5/2013
1/6/2013
1/7/2013
1/8/2013
1/9/2013
Abu Dhabi, United Arab Emirates
1
1
1
3
2
2
2
2
2
Adalaj, Gujarat, India
4
4
4
3
2
3
3
2
2
Addison, TX
2
2
3
3
4
4
3
4
3
What did my real data looked liked?
it has 380 columns and 500+ Rows and so MANUAL copy pasting was NOT an option!
Excel 2010 Solution:
It’s so simple!
Step 1: Select the data > COPY (Shortcut: ctrl + c)
Step 2: Switch to a new/different excel sheet
step 3: Paste Special > Transpose (T)
So After doing this, This is how the Input & output looks:
Conclusion:
In this post, We saw how to swap or reverse the axis of a table data in Excel 2010.
I found some data-sets on Occupational Employment Statistics on Bureau of Labor Statistics site and I played with it to see if I can find something interesting:
Few things about the data & visualization that I am going to share
US only
I downloaded the national level data But there’s also state level data available if you’re interested to drill down.
The reports that you see where created after I got a chance to “clean” the data-set a bit and created a data model that suited basic reporting on top of it.
For this blog post, I am going to play w/ May 2010 & 2011 data
With the help of original data-set, you can drill down to get statistics about a particular Job Category if you want. For this blog-post, I am going to share visualizations that correspond to Job categories.
click on images to see the higher resolution image.
With that, Here are some visualizations:
1) Job Category VS mean hourly salary:
2) Job Category VS number of employees:
3) Scatter Plot:
X Axis: Number of employees
Y – Axis: Wage (Mean Hourly Salary May 2011)
Size of Bubble: Wage (Mean Hourly Salary May 2011)
*Note: This may not be the best approach to create the Scatter Plot as I have used the same value (Mean Hourly Salary May 2011) twice – But since I was just playing w/ it, I went with what I had in the model.
Here’s the visualization:
Some of the things I observed:
1) I belong to an Industry (Computer and Mathematical occupations) which has relatively higher mean hourly wage.
2) There are few people working in “farming, fishing & forestry occupations” that do not get paid much.
3) There are lots of people working in “office administrative support occupations” that do not get paid much.
4) Management Occupations, Legal Occupations and computer & mathematical occupations have relatively higher mean hourly wages.
Conclusion:
In this post, I played w/ Occupational Employment statistics data-sets and shared some visualizations.
In this post, we would see how to get started with Fuzzy look-up add-in for excel.
First up, Which problem does Fuzzy Lookup add-in for excel solve? It cleans similar (a.k.a matching) textual data in Excel. E.g. “Mr Paras Doshi”, “Doshi Paras”, “Paras A Doshi” are similar and may refer to the same person – Fuzzy look-up helps you detect such similar textual data. This add-in is really handy if you are “combining” data from different systems where the data is not in the same format – using this add-in you can detect similar looking text and clean the data-set at hand. With that, Here are the steps to download, install and play with this add-in:
1) Download “Fuzzy Lookup add-in for Excel. Read: Over view, system requirements and Instructions
A short Blog-Post explaining what each “term” means in Access. If you are interested, introduction” to Access is here (via Wikipedia).
Here are the details:
1. Tables:
The place where data is stored. Access is a “data store” which allows us to store data in Rows & Columns format. Here are couple of things that you should know about Tables:
1a. Tables can be related: If you come from the database world – you know that tables can also have relationships among them. If not, just think of relationships as a way to link similar data items. For example, Product Table having Product-ID column can be related to Product-Category table having Product-ID column. Benefit? The Product-Category column can have details about the particular category and since it is linked to the Product Table you do not have to enter the details about the category in the product table again & again. Saves time (and storage space) by eliminating redundancy.
1b. Columns in the Tables have Data-Types: You can specify the data-type of a column/field. So you can say that Column A will contain text data, Column B will contain Numeric Data.
1c. Each row added in a table is called a record
2. Forms:
Forms are used to “input” data into Tables. Think of forms as “cards” that allow you to enter data into tables one field at a time. Now, if you are new to Access, you know that you can enter data while creating/designing tables without creating forms – then why do you need forms? Let me give you an example: In an organization, Person A designs Access Tables and Person B who is not access-savvy uses it to enter data. Now, it makes sense to abstract/hide the “technology details” from the person who is not access-savvy and in that case, creating forms helps person B in entering data without worrying about the underlying table-structure.
3. Queries:
Queries gets it data from “Tables”. Why do you need queries?
3a. Queries help you “find” data from your tables. You can specify criteria like fetch data for month of January 2011.
3b. Combine data from more than one table
3c. Edit/change data. (adding a criteria is optional)
3d. You can delete data. (adding a criteria is optional)
4. Reports:
Once you have your queries/Tables that need to be “outputted” (or say printed) – you can create reports. Access has a nice Report Wizard that would walk you through steps that are needed to create a report.
That’s about it for this Post on a Database Management System! How do you use Access in your Organization or personally? Speak up in the Comments section!
