Live tweeted #sqlpass’s Business Analytics VC webinar: 13 Excel Tips!

I was live tweeting during our monthly PASS Business Analytics VC meeting, Here are the tweets to learn about 13 Excel Tips!

Thanks everyone who attended, I hope it was helpful!

Here are some ways to follow the Virtual Chapter:
Website: http://bavc.sqlpass.org/
Youtube: https://www.youtube.com/channel/UCOiRAA4gBxEeVxwmEZ1qy1w
Twitter: https://twitter.com/passbavc
LinkedIn: https://www.linkedin.com/groups/PASS-Business-Analytics-Virtual-Chapter-6701113

PASS Business Analytics VC’s Online Event: “Power BI Info Management and Data Stewardship”

Power BI is an exciting new technology in the business analytics space from Microsoft. I’ve played with its current preview version & attended couple of sessions on Power BI at PASS Summit 2013. Based on my first impression, I noted down Problems that Power BI solves. Note that as of today, it’s in preview & so information around cost is not availale yet but I try to learn and understand as much as I can Today about how Power BI is going to help business users & power users in the future. As a part of that, I’m attending Business Analytics VC’s session on “Power BI Info Management and Data Stewardship” by Matthew Roche & Ofer Ashkenazi on Nov 7th 12 PM EST.

Topic: Power BI Info Management and Data Stewardship

Date & Time: Nov 7th 12 PM EST

Here’s the Link to register: http://bit.ly/PASSBAVC

Topic Abstract:

“Business intelligence tools continue to improve, letting users shorten their time to insight and take that insight to more devices in more places. But this evolution of BI doesn’t change one fundamental fact of information management: You can’t gain insight from data you can’t access.

In this session, Matthew Roche and Ofer Ashkenazi will introduce the role of the data steward and the self-service information management capabilities included in Power Query and Power BI for Office 365, focusing on how Power BI empowers business users to add value to the organization.”

Closing note:

I recently volunteered at Business Analytics VC as VP of Marketing, so it’s in my interest to spread word about the event but I would not spread word about something unless it gets me personally excited about it! :)

I hope to see you at the session and for some reason if you can not make it, we usually record sessions & so you can check out the meeting archives section of the PASS BA VC site after the event.

How to Change Data Source of an Excel 2010 Pivot Table?

Problem:

There’s an excel 2010 pivot table that’s getting its data from TestServer. How do I change the data source so that the Pivot Table get’s it data from ProdServer?

Note: This is a common scenario among Business Intelligence Developers who might want to switch between different servers (Test/Prod) to for comparing data.

Solution:

1. Establish the connection to a NEW data source via Data Tab in Excel.

2. Select the Pivot Table whose connection needs to be changed.

3. From the Excel Toolbar. Go to Pivot Table Tools > Options > Change Data Source

Pivot Table Change Data Source4. Choose the NEW connection from here. (you can use the connection that you created in step #1)

Choose the new data source Pivot Table5. Click OK and that should have successfully changed the data source for you. Please test the column names/values that might have been affected since you changed the data source.

That’s about it for this post. Your comments are very welcome!

How to change the layout of the Excel Pivot Tables?

Pivot Tables are powerful mechanism to analyze data. And as you may know, it’s really popular among business professionals that use excel for their day-to-day data analysis needs. With that context, Here’s the blog post on Pivot Tables Layout:

Situation:

I got a question from my client who had a specific need on how she wanted her Pivot Tables to look. Here’s how the Pivot Table should look like:

excel pivot table power pivotSolution:

I am using Excel 2010 Pro Plus to demo the solution, please use this solution to adapt to use it with other versions of excel.

Without formatting and changing the layout, the excel pivot table looked like this:

step 1 excel pivot table from power pivot

Now, let’s work step by step to meet the requirement:

Step 1: click somewhere on the Pivot Table and from the Toolbar, Switch to the Design Tab under PivotTable Tools

Pivot Table Tools Design ExcelStep 2: Now here, Go to Report Layout > Show in Tabular Form

Pivot Table Layout Tabular Form

Step 3: As you might have noticed the “hierarchical” structure is now broken up into multiple structure getting us closer to meet the requirement.

half way pivot table layout

Step 4: In our requirement, you can see that it does not give the user to see the “+” or “-” (expand, collapse) buttons. So, let’s hide these buttons from the report from Step #3

Right click somewhere on the pivot table > PivotTable Options > Display tab > uncheck the box that says “Show expand/collapse buttons“:

pivot table hide expand collapse button

Step 5: so now it looks as follows:

excel pivot table power pivot

So as you can see, it meets our requirement now.

To recap, Here’s what we had to do:

#1: Change the Layout of the Pivot Table

#2: We saw how to hide the expand/collapse buttons too.

 

That’s about it for the post! Thanks for reading.

your comments are most welcome!

 

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

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:

data explorer unpivot excel

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:

data explorer unpivot excel 2

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:

data explorer unpivot excel 3

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.

data explorer unpivot excel 4

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:

data explorer unpivot excel 5

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:

Your comments are very welcome!

 

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

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.

data sources explorer excel

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:

merge join excel data explorer

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:

merge join excel data explorer 2

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.

merge join excel table data explorer 3

6) This is how the output looks:

merge join excel table data explorer 4

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

merged data join table visualized excel 3

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:

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

data explorer excel remove duplicates

4. After you’ve clicked on the From Table, a query editor will pop up:

excel data explorer query editor

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

data explorer remove duplicates excel

7. click on done if you see that the duplicates have been removed correctly

data explorer excel remove duplicates 2

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.

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

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:on time flight arrivals excel without trendline

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

add trendline time series

2. configure the trend-line options

trend line configuration options excel

3. I also changed the line style

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

american airlines on time flight arrivals excel with trendline

Conclusion:

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

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

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:

Data Set Excel 20132. 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:

Predictive Data Entry by Excel 2013I hit enter for the column First Name.

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

FLASH FILL options excel 2013That’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?

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)

1 sql server data quality services data source 1 excel file

File 2: (CSV)

2 sql server data quality services data source 2 csv file

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:

3 data quality services domain management values sql server

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:

4 data quality services sql server integration services

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

5 data quality SSIS data cleaning transformation

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

6 standardized output SSIS gender data quality services

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