By default, its hierarchical but I want the data in a Tabular/Flattened format. How do I do that? I am going to show how to do that with Excel 2010.
Default View (Note the Hierarchical view of Pivot Tables)
BEFORE
After I Flattening it, It should look this:
AFTER
Note: it also depends on data, not every data can be represented in the flattened view. In above example, we’re analyzing each product (evident by product id) and so it supports the flattened view.
here are the steps:
1. Select Pivot Table
2. From the Toolbar, Turn off Subtotals. Go to Pivot Table Options > Design > Sub Totals > Do not show Subtotals
3. Go to Pivot Table options > Design > Report Layout > Show in Tabular Form.
That’s about it. once you do that, you should see data in flattened format.
There’s a Power Pivot Model. It imports some number of columns from a table. Now, there’s a need to import an additional column to this table. How do you do that?
Solution:
Here are the steps:
1. Open Power Pivot Model.
2. Go to Design Tab.
3. Click on Table Properties:
4. From here, You can add additional columns:
5. click on save and that should add the column to your model:
FAQ:
#1: I can’t see what you see inside Design Tab. What do I do?
#2: How about changing the data source & pointing to new cube/database? It’s very helpful when you switch between QA, Dev or PROD servers. Here are the steps:
you can do that too! Go to Design > Existing connections > Select Connection > Edit > Make changes > Test it! > Save > “Refresh”. Any Problems? No? Great. That’s Done!
Data Explorer add-in is amazing! It’s helps you: combine, find and re-shape your data in Excel 2010/2013. I’ve blogged about: 1) How to merge Table Data and 2) How to clean duplicate data and now in this blog post, I want to share a step-by-step on Unpivoting data using the Data Explorer add-in.
What is un-pivoting? I hear you ask. Instead of explaining it, let me share an Image:
BTW, the above data is from my Facebook Page Insights.
So our problem statement is (please refer to above Image): we are given table blue and we need to output table green. In other words, we need to Unpivot the data.
Solution:
Here are the steps:
1) Open Excel, Open Data Explorer add-in. And Connect to your data. Wait when you see the Query Editor.
2) (Optional) In the Query Editor, Rename the query. I renamed it to “Unpivot Data”. And this how my query editor looks:
3) Now, Select the columns that need to be unpivoted > Right Click > Unpivot Column
Note that I’ve selected all columns that I want to UnPivot:
4) You’ll see the updated results in the query editor window. I renamed the columns “Attribute” to “Age and Gender” and “value” to “reach”. If you want to rename the columns, select the column > Right click > rename.
If everything looks OK, click on Done in the bottom right corner
5) There you have it, Unpivoted data in Excel 2010/2013 using Data Explorer add-in!
And then its super easy to create charts, Here’s one I created after I had unpivoted the data:
Insight: For my blog, my Target Audience seems to Male between the age of 18-24 and then 25-34.
FYI: The Date Range of the Data Set of 1st Jan 2013 – 25th Apr 2013.
That’s about it for this post, Here are some Related articles:
Merging/Joining/Combining data-sets in Excel has not been an easy task. There are third-party add-ins that makes it easy but out of the box, excel didn’t have an easy way to merge/join table data. But now with the Data Explorer add-in, we have an add-in that let’s us merge/join data in excel w/ few clicks.
Input is Table 1 & Table 2. The output we need is merged Table.
Table 1:
Date
Daily New number
Month
1/1/2012
0
1
1/2/2012
0
1
1/3/2012
0
1
1/4/2012
0
1
1/5/2012
0
1
1/6/2012
0
1
1/7/2012
0
1
1/8/2012
0
1
1/9/2012
0
1
………………………………..
Table 2:
Month
Month Name
1
January
2
February
3
March
4
April
5
May
6
June
7
July
Merged Table:
Date
Daily New number
Month
Month Name
1/1/2012
0
1
January
1/2/2012
0
1
January
1/3/2012
0
1
January
1/4/2012
0
1
January
1/5/2012
0
1
January
1/6/2012
0
1
January
1/7/2012
0
1
January
1/8/2012
0
1
January
1/9/2012
0
1
January
………………………………
Solution:
Let’s see how data explorer can help us Join/Merge Table 1 & Table 2.
1) create query that connects to Table 1 & Table 2.
2) Once you have queries that connect to the tables need to be merged, then click on Merge
3) Once you click on Merge, you’ll see a dialog:
Here you need to configure three things:
a) First Table
b) Second Table
c) Columns that will be used to merge/join data
In this case, this is how my merge dialog looks:
4) Once configured correctly, click on OK. You’ll see a dialog box where you can configure the output of the merged table. click on the new column to see the options that are available to you to configure the output of the merged table:
5) In this case, I’ve selected just one column month name that needs to be merged. You can also explore the aggregate tab in case you’ve numbers that needs merging.
6) This is how the output looks:
7) Rename the new column.
Select the new column > Right Click > Rename
8) Click Done if it looks OK.
9) The merged data is now available to you in Excel!
And one can analyze it!
Let’s see before and after. Note that instead of month numbers, we now have month names
Conclusion:
In this post, we saw how to merge/join/combine data from two different sources in Excel 2010.
In this blog post, we’ll see how you can remove duplicated and clean data in excel tables using Data Explorer Add-in.
Problem:
Our Excel Table has following Data:
Month
Month Name
1
January
1
January
1
January
2
February
2
February
3
March
And we want to remove duplicates to make the data-set look like this:
Month
Month Name
1
January
2
February
3
March
In real world data-sets, we wouldn’t have few rows but lot’s of rows and doing it manually wouldn’t be the wisest option. With that in mind, let’s look for a few-clicks solution that can help us remove duplicates.
Solution:
If you haven’t already, download the Data Explorer add-in preview available for Excel 2010 & 2013. It can do a lot more than removing duplicates – it’s a great add-in and it’ll save you lots of time especially if your job involves discovering, cleaning and combining data for analysis purposes. After you’re done installing the add-in, use the steps below to remove duplicates in an excel column:
1. Open Data in Excel. Switch to Data Explorer Tab
2. For the purpose of the demo, I am assuming that you already have the data in excel file. If not, you can connect to other sources via the add-in.
3. Data Explorer add-in > Excel Data> From Table
4. After you’ve clicked on the From Table, a query editor will pop up:
5. Select both columns
(you can select both columns by: select first column > hold down the ctrl key and then click on second column)
6. Right click > Remove Duplicates
7. click on done if you see that the duplicates have been removed correctly
Conclusion:
In this blog post, we saw how to remove duplicates and clean data in Excel using the Data Explorer Preview add-in.
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!
