Exploring Excel 2013 for BI Tip #3: Flash Fill

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Flash Fill

Flash Fill is new functionality in Excel 2013 that allows you to manipulate data and fill a column based on actions you took.  I like to think of it as somewhere between programming a macro and using the black cross to pull down formulas.  I am going to illustrate a couple of ways to use this then you will have to take it from there.

First, you will need a set of data that this makes sense with.  So let’s start with something obvious.  You have a column of data that has a customer’s name formatted as Firstname Lastname.  But you really need to format them as Last name, First name. 

Here is the dataset we will use (it is the Indiana Hoosier players from the 80-81 season who were drafted).

Year Round Pick Player NBA_Club Key
1981 1 2 Isaiah Thomas Detroit Pistons 1
1981 1 18 Ray Tolbert New Jersey Nets 2
1981 5 115 Glen Grunwald Boston Celtics 3
1981 8 180 Steve Risley Phoenix Suns 4
1982 10 225 Landon Turner Boston Celtics 5
1983 1 22 Randy Wittman Washington Bullets 6
1983 2 40 Jim Thomas Indiana Pacers 7
1983 2 41 Ted Kitchel Milwaukee Bucks 8
1983 4 78 Steve Bouchie Detroit Pistons 9
1983 7 141 Tony Brown Indiana Pacers 10

So in our first illustration, we will take the Player column and separate and reorder the name – Last name, First, name.

The Flash Fill function is located on the DATA tab as shown below.


Follow the next steps carefully to reproduce what I am doing.

  1. Highlight the Isaiah Thomas last name and copy it to an empty cell.
  2. Add a comma.
  3. Copy his first name over.  This should have a column where you see, Thomas, Isaiah.
  4. Highlight the cell with the new value and click Flash Fill.

You are done.  You now have a column that is formatted the way you wanted it to be.  Cool, huh?

imageThe next set of data I am going to work with is the Hoosiers 2011-2012 season.  The data includes a Record column which has the conference record part of the way through the set.  What I want is to have separate columns for the overall and conference records.

This time I am going to select the cell on row 15 which has both records in it.  The first column I will create will have the overall record.  This is done by copying only the conference record over and flashfilling the column.  Works great.  The first row will have no data because the dash does not fit the pattern.  The next column, I only copy the section in the parenthesis.  However, this did not work.  For the rows without a set of data in parenthesis, it copied that anyway.  Let’s clear the column and try one other way.

After many attempts that did not imagework, then next operation needed more patterns to follow.  For rows 13 and 14, neither of which has a conference record, I placed an empty set of parenthesis (), Then for the conference record, I copied the conference record in parenthesis over.  My results look like the table to the right.  As you can see, it is pattern matching and sometimes it does not get you the results you would expect.  However, it is a great tool and will get you most if not all of the way to the result you need with very little effort.  Enjoy.

Exploring Excel 2013 for BI Tip #1: Quick Explore

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Quick Explore

So, you have connected Excel to your SSAS cube.  You really wish you could cross drill easily in the product.  If you have used PerformancePoint Services you know the process.  Right click on the bar or cell and then choose the dimension to drill to using the information you clicked on as a starting point.  You can now do this in Excel 2013 using Quick Explore.  Here’s how to do it.

1. Click on a cell and hover over it.


2. Click the Quick Explore button a.k.a. magnifying glass and lightning bolt.  That will pop up the following dialog box.


In this box, you can see that the cell has focus on “Boston” based on context and is drilling into the Geography hierarchy of the Client dimension based on the selected filters and slice of data we are focused on.

3. Click the Drill To target.  Excel will create a new view of the Pivot Table with the Drill to on the row as shown here with the filter still in place.


4. Going back…  One of those frustrating things with this is how do I go back to my original view.  Ironically, you use Undo.  This will reset the view back to the previous.  So, if you choose to drill into this data again and again, you have to Undo that many operations to get back to the starting point.  Of course, can click the arrow by the arrow to see your “bread crumb” and pick where you want to go back to as well.


Until the next tip…

Excel 2013 Will Impact BI Users

Excel 2013 IconAs noted in that great text, The Hitchhiker’s Guide to the Galaxy, “Don’t panic!” Excel 2013 will make even more capabilities available to BI users.  This includes having xVelocity natively supported in the tool which makes much of the PowerPivot capabilities available in a spreadsheet which used to only be available in the PowerPivot window.  It also adds the ability to create Power View reports in Excel.

Check out more on my thoughts about this on Magenic’c blog.

PowerPivot and Market Research

This past Tuesday, February 14, I was invited to discuss how technology can be used to help analyze data for the purposes of Market Research at Minnesota School of Business.  A friend of mine, Shane Smith, is teaching this class and invited me to show how the tools I work with every day can help his students analyze data for marketing.

The Setup

The class was analyzing the results from a survey they conducted at the school as a part of a market research project.  The survey had five questions with various types of answers such as Yes/No and multiple choice.

Shane wanted to show the class that using technology, they could glean more results from their survey.  This process would also help them understand the effectiveness of the survey they created.

The Data Prep

For my part, I wanted to introduce them to what I do – business intelligence solution design and implementation.  I also wanted to introduce them to the specific tools which they could use to analyze their results for the project.  In this case, we were using Excel 2010 and PowerPivot for Excel 2010 (SQL Server 2008 R2 version).  The final goal was to help them see that these tools can be used to analyze data in real world scenarios.

Shane provided me with the initial survey results in an Excel spreadsheet.  I took that data and flattened some of it out and turned some of it into data that math could be performed on.  For example, I took a multiple choice survey question with 5 possible values and turned it into 5 columns of data with 1 and 0 as possible values so additional calculations could be done on it.  We also created the equivalent of a dimension by taking one of the multiple choice questions and creating a new sheet with possible values and labels.

The Cool Stuff

I took the sheet we updated the data on and I walked the class through the process of bringing that data into PowerPivot.  This included building a relationship between the dimensional data and the primary survey results which were on two sheets in the source workbook. From there, we generated a couple of pivot table views and some charts.  Next, they came up with other ideas of what data to look at and how they wanted to see it presetned. I walked them through the process of prepping the data as necessary.  Then we created more visualizations and presentations of the data that helped them better analyze their results.

The Wrap Up

Kudos to the Microsoft team that put this toolset together.  These students, who are not technologists, gained confidence in the use  of PowerPivot for their project. They are also now set up to use it in the future for similar projects or even their jobs. Because of its ease of use, it was a great demo and training session on using tools to analyze data.

I had a lot fun sharing the technology that I work in regularly with users who would not have tried to do this on their own.  Tools like PowerPivot enable users to turn their data into usable information on their own.  While there will always be a need to build enterprise solutions, this fills a gap in the marketplace with a tool many are already familiar with.

I hope the students are able to continue to use the tool and find a place for it in their workplace in the future.  If you have not yet given PowerPivot a test run on your own, you can find it here: http://www.microsoft.com/en-us/bi/powerpivot.aspx.  We used the 2008 version, but feel free to give either a test run.  Keep in mind that you need Excel 2010 to use PowerPivot.  Best of luck to all of them and any of you who give it a test run.  Feel free to share what your experience with PowerPivot here in the comments.

PowerPivot for Excel Install Issues

At one of my customers, we had a very frustrating experience while trying to get PowerPivot for Excel (SQL Server 2008 R2) installed on a number of developer machines.  All of the machines were 64-bit running Windows 7. We had Office 2010 32 bit installed in all cases.  The frustration occurred around the error message.

PowerPivot Error Message

We verified we had downloaded the correct version and according to the following Microsoft Support article, http://support.microsoft.com/kb/982765, we also confirmed all of our prerequisites were in place.  After going through this verification process, we had one of the developers completely reinstall MS Office and then install the add-in.  However, we were still unsuccessful.  So, we did what all good developers do and got our local desktop support guy, Steve Schuler, involved.  He was able to successfully work through the issue.

Here are the steps that Steve discovered which allowed us to successfully get the PowerPivot add-in installed.

  1. Save PowerPivot_for_Excel_x86.msi to a local folder.
  1. Right click on Command Prompt and choose Run as Administrator.
  1. Type msiexec/i c:\data\PowerPivot_for_Excel_x86.msi  into the Command window.
  1. Launch Excel and choose Accept when prompted to install PowerPivot.

If PowerPivot does not show in the ribbon, go to File-Options-Add-Ins and select Com Add-In’s from Manage drop-down list and choose Go.  Confirm that PowerPivot for Excel is selected.  If it is not selected, select it and then choose OK.

The root cause of the issue appears to be related to the security policies and administrative privileges on the machines. In our case, we are all local admins and should not have encountered the issue.  We had inconsistent experiences on many machines that were tested.  In some cases there was no issue and then in other cases we could only install with the steps above.

Thanks again to Steve for a job well done.