Unpivoting data using the data explorer preview for Excel 2010/2013:

Posted on April 25, 2013 by Paras Doshi • Posted in Business Intelligence, Data Analysis, Microsoft Office • Tagged Business intelligence, Column, Data Explorer, DataSet, Excel, Excel 2010 & 2013, Excel 2010/2013, Microsoft, Spreadsheet, Tables • 2 Comments

Introduction:

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.

Before we begin, If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx

Problem:

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:

Remove Duplicates in Excel Tables using Data Explorer Add-in: (parasdoshi.com)
Merging/Joining datasets in Excel using Data Explorer add-in (parasdoshi.com)
PASS Business Analytics Conference Keynote Day #1 (parasdoshi.com)

Your comments are very welcome!

Merging/Joining datasets in Excel using Data Explorer add-in

Posted on April 24, 2013 by Paras Doshi • Posted in Business Intelligence, Data Analysis, Microsoft Office • Tagged Data Explorer, DataSet, Excel, Excel 2010 & 2013, Microsoft, Microsoft Excel, Spreadsheet, Tables • 2 Comments

Problem:

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.

If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx

Situation:

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

In this post, we saw how to merge/join/combine data from two different sources in Excel 2010.

Remove Duplicates in Excel Tables using Data Explorer Add-in: (parasdoshi.com)

Remove Duplicates in Excel Tables using Data Explorer Add-in:

Posted on April 23, 2013 by Paras Doshi • Posted in Business Intelligence, Microsoft Office • Tagged Column, Data Explorer, Data set, DataSet, Excel, Microsoft, Microsoft Excel, Spreadsheet, Spreadsheets, Tables • 3 Comments

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.

If you’ve not downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx

Note:

1) URL to download the add-in may change in future

2) The steps that I described may also change because as of today the ad-in is in “preview” stage and things may change in future.

PASS Business Analytics Conference Keynote Day #1 (parasdoshi.com)
Data Explorer for Excel: An experiment with Sachin Tendulkar (romitmehta.com)
Found something interesting by exploring a “List of companies by revenue” Data Set: (parasdoshi.com)

Adding a TrendLine to a Time Series Line Chart in Excel 2010:

Posted on February 12, 2013 by Paras Doshi • Posted in Microsoft Business Intelligence, Microsoft Office • Tagged Excel, Excel 2010, Math, Microsoft Excel, Spreadsheets, Statistics, Time series, Trend line (technical analysis) • 1 Comment

I was playing w/ a time series data set in Excel 2010 and learned how to add a Trend-line and in this blog post, I’ll share how I added it:

First up, How is Trend-line useful? Here are few answers:
- It helps us see how data is changing over time, in other words, it helps us find “trends”
- It helps us forecast future.

With that, here is the chart without Trend-line:

Now let’s add the trend-line and you’ll be able to compare on your own how Trend-line makes it easier to spot “trends”. Here are the steps:

1. select the line > right-click > add trend line

2. configure the trend-line options

3. I also changed the line style

4. And Here’s the chart w/ trend-line

Conclusion:

In this post, we saw how to add trend-line in the time series chart in excel 2010

Need to combine two chart types? Create a combo chart and add a second axis (blogs.office.com)

Trying out FLASH FILL which is a new Excel 2013 Feature:

Posted on February 6, 2013 by Paras Doshi • Posted in Microsoft Business Intelligence, Microsoft Office • Tagged Cell, Excel, Excel 2013, Flash, Microsoft Excel, Microsoft Office, Spreadsheet • 1 Comment

I learned about an interesting new feature in Excel 2013 which they call “FLASH FILL”. Instead of trying to explain it, Let me demo it:

1. I’ve following data in Excel 2013:

2. Now the requirement is to split the FULL NAME into FIRST NAME and LAST NAME.

So I entered Kim in cell B2

Then I typed just Mi in the cell B3 and Excel 2013 predicts correctly for cell B3, B4 and B5 that I am filling up the first name from the Full Name column.

See:

I hit enter for the column First Name.

I performed similar steps for the column Last Name. And there you have it:

That’s about it for demo of Excel 2013′s Flash Fill – very cool!

I’ve posted how to split the values in a column for Excel 2010, you can read that here: How to split the content of one excel cell into separate columns?

Your comments are very welcome!

How to standardize data using Data Quality Services?

Posted on December 12, 2012 by Paras Doshi • Posted in Data Quality Services, Microsoft Business Intelligence, Microsoft Office • Tagged Data quality, Database, DQS, Knowledge base, Microsoft Excel, Microsoft SQL Server, sql, SQL Server Integration Services • 9 Comments

I was playing with Data Quality Services when I though that it would be nice if I could implement what I had learned and create a quick demo that shows how to standardize data using the technology. So here it goes:

Note: This is not a step by step Guide – I am assuming that you are aware of different pieces of DQS. I am going to post a brief outline of how did I go about implementing a quick demo.

Technology used:

1) SQL Server 2012 Data Quality Services

2) SQL Server Integration services to show the DQS cleansing component in action.

Problem:

We want to standardize to Gender to have only two values M & F – but now data coming from two files creates data inconsistency issues:

Two files that look like this:

File 1: (Excel)

File 2: (CSV)

Solution:

Let’s see how SQL server 2012′s data quality services would help us solve this problem:

1) I created a Knowledge base with a domain Gender and created domain values that looks like this:

2) Tested the Knowledge base quickly by creating a Data Quality Project. It worked!

3) Switched to SSIS and created a Data Flow which uses following components:

What would be interesting to you to see mapping tab in the DQS cleansing transformation component, so here it is:

5) And let me show you the Target File – note that you can choose to NOT have Gender_Source, Gender_Status & Record Status. The column of interest is Gender_Output

That’s about it for this post. I hope this helps!

How to Delete a Knowledge Base in SQL Server 2012 Data Quality Services? (parasdoshi.com)
How I think of what is KnowledgeBase in SQL Server 2012 Data Quality Services (parasdoshi.com)
Guest Blog: How we use Fuzzy Lookup add-in in our company to solve data inconsistency problems: (parasdoshi.com)
Master Data Services: Analogy to remember what are Models, Entities and Attriutes (parasdoshi.com)

How to import data from Excel file to SQL Server:

Posted on December 10, 2012 by Paras Doshi • Posted in Microsoft Office, SQL, SQL server • Tagged Database, Excel, FAQs Help and Tutorials, Microsoft Excel, Microsoft SQL Server, Select (SQL), sql, sql server, SQL Server Integration Services • 1 Comment

In this post, we would see how to import data from Excel file to SQL Server using Import and Export Wizard, we would import data from four excel worksheets to SQL server. Here are the steps:

1) Right click the Target Database > Tasks > Import Data to open the SQL Server Import and export wizard

2) select the Data Source. In this case, Excel file. You would select the worksheets to fetch data from later, for now – select the excel file as shown below:

3) choose the Target Database:

4) Select the tables (in our case- worksheets) or specify a query. For the purpose of this demo, we would go with “copy data from one or more table or views” option:

5) Select the worksheets from the Excel file and also specify the tables in the target systems. If the tables in the target database are not already created, then would be created by this wizard:

6) You have the option of running this wizard immediately or you have the option of creating a SSIS package and then run it later. For the purpose of this post, we would select Run Immediately

7) Review the information and click Finish

8) After successful execution:

Conclusion:

In this blog post, we saw how to import data from Excel file to SQL server.

Guest Blog: How we use Fuzzy Lookup add-in in our company to solve data inconsistency problems: (parasdoshi.com)

Guest Blog: How we use Fuzzy Lookup add-in in our company to solve data inconsistency problems:

Posted on December 6, 2012 by Paras Doshi • Posted in All about Data!, Microsoft Business Intelligence, Microsoft Office, SQL server, Technology • Tagged Data Quality Services, Data set, Database, Microsoft SQL Server, sql, sql server • 9 Comments

This is a Guest Blog from Mantresh Jain.

About Mantresh Jain:

Mantresh Jain is a C Level Executive at SMB in manufacturing domain based out of India. He has bachelor’s degree from a business school. And he holds a special interest in how businesses can leverage newest Information Technology Tools for optimizing business processes. He is working on a company-wide ERP implementation and is a single point of contact for the implementation process. He spends his free time on computer games of all kinds! Link with him here: http://www.linkedin.com/pub/mantresh-jain/43/562/749

How did they discover Fuzzy Look-up add-in for Excel (A write-up by Paras)?

Some months ago, Mantresh approached me to see if I knew any tool that would help him deal with “messy” data. On Further questions, I learned that

- Messy data = lots of duplicates

- Uses SQL Server Express & do NOT have plans to upgrade to SQL Server versions that include Data Quality Services and/or Master Data Services. Remember the context here: They are a small and medium size business.

- Do use Excel – a lot!

- Do not have folks w/ “SQL” knowledge

With this requirements, I asked him to see if an add-in for excel called “Fuzzy Lookup” meets their need. After trying it out: here’s Mantresh’s experience of using Fuzzy Lookup add-in for Excel in their organization:

Summary:

In my company we are implementing ERP software. I faced a problem of Data migration from two fox Pro based software’s to SQL (for ERP)

More Details:

Two fox Pro Software’s worked independently form each other. And as a result each of them had their Separate Databases.

Lets Call them FX1 and FX2.

Now I wanted to import Account Master Data from them to SQL, Here are the fields in our Account Master data:

Name, Address, Bank Details, Phone Number among other fields

Problem

Both systems had issues of data Duplication and Data Inconsistency

To give you an example, I faced following problems:

1) FX1 had around 3500 entries and FX2 had 2400 entries

Now in FX1 out of 3500 around 2000 were same as FX2

Also FX2 had around 2000 entries same as FX1

Now i wanted to import only unique Account Master gathered by “combining” the two systems to SQL.

Example:

FX1 has “VMS Industires” while FX2 has “V.M.S Industries”

Solution

Fuzzy Look up add-in for Excel.

Step 1) Import data from both databases to excel

Step 2) Using Fuzzy Look up to find data matching to each other based on variable conditions that we select.

Step 3) It reorganizes data as

FX1 entry	1st matching FX2 Entry
	2nd Matching FX2 entry
…	…

This is how we find Duplicate entries and then clean our data-set

Benefit

If not for Fuzzy Look I would have had to manually match each entry to each other which would have taken estimated 60 to 100 Man Hours but with Fuzzy Look-up, we did the job in 24 Man Hours Only.

———————-

Conclusion by Paras:

Thanks Mantresh for sharing your experience!

And here’s a related post:
How to clean similar textual data in Excel via Fuzzy lookup add-in?

Where’s the Formula Bar in Excel 2013?

Posted on November 23, 2012 by Paras Doshi • Posted in Microsoft Office • Tagged Data model, Excel, Excel 2013, FAQs Help and Tutorials, Microsoft, Microsoft Excel, Spreadsheet • Leave a comment

I was playing with Excel 2013 and wanted to see the Formula Bar for something. Turned out, by default, It was hidden and so I learned how to unhide it. Here are the steps:

1. Go to View Tab

2. Check the “Formula Bar” check-box:

3. And it’s unhidden now!

Conclusion:

In this blog-post, we saw how to unhide the formula bar in Excel 2013.

Intro to Power View for Excel 2013 (blogs.office.com)
Calculated Member and Measures in Excel 2013 (blogs.office.com)

PowerPivot: Quick Tip regarding Copy-Pasting Tables in PowerPivot 2010

Posted on October 22, 2012 by Paras Doshi • Posted in Microsoft Business Intelligence, Microsoft Office, Power Pivot • Tagged Data model, Microsoft, Microsoft Excel, Microsoft SharePoint, Models, PivotTable, Powerpivot • 2 Comments

I was working on couple of PowerPivot models recently and both models needed a Date Table. So here’s what I did:

1. Imported the filtered version of the Date Table (DateStream) from Azure Data Market. Added Calculated Columns to the table. Let me call this Table1

2. I copied entire Table1 and pasted it to an another PowerPivot model – let’s call that Table2

After this:

And I went to happily create data models! Later, I while testing the model (that had Table2), I realized that it had lost the “formulas” – in other words when I copy-pasted from Table1 to Table2 in PowerPivot – it did not copy-paste the formulas but it just pasted the values.

So, 1. Copy Pasting a Table in a PowerPivot does not copy-paste’s the Formula’s in the columns.

And also it does not keep the “Data connection”. In this case, Table1 was connected to Azure Data source and I could “refresh” data if I chose to. But Table2 was “Static” – In other words, It lost the data connection with the data sources. So I could not “refresh/update” data from the data source if need be.

S0, 2. If you copy Paste a Table in a PowerPivot, the newly copied table does not keep the “Data connection” to data source.

I have also posted this here: http://beyondrelational.com/modules/1/justlearned/tips/17976/power-pivot-copy-paste-a-table-in-a-powerpivot-does-not-copy-pastes-the-formulas.aspx