Kasper de Jonge PowerPivot Blog

I had an interesting question today where someone send me a workbook that contained a flattened table like this:

Now as a result we want to see the top 1 nr of products per country. The problem here is that we cannot just load this data into PowerPivot and start analyzing to get this result. In this case the columns need to become attributes in a dimension. This is a classical ETL operation that can be done by many tools like SQL and SSIS , but not by PowerPivot as a modeling tool.

So how do you fix this problem ? At first I used a Macro that we found online to unpivot the data, that did get me the result I wanted. But some time later I realized that there is a trick in Excel that I can use that I have blogged about before here, by combining this trick and another it will be pretty easy to unpivot.

In this blog post I will show you how to Unpivot a flattened table using PowePivot and Excel (Actually you would not even need PowerPivot if you don’t want to).

The first thing we do is create a regular excel pivottable out of the table I showed in the first image

Next we change “Count of Product x” to “Sum of Product  x” and change the pivottable into a classic pivottable using Pivottable options:

This will turn it into a column per level:

Next we move the values from Colum to Rows, this results in an unpivoted result.

Next we need to turn the measure “Sum of Product X” into a column. Copy the entire pivottable and past it in a new sheet keeping only the values

This will result in a flat table inside Excel. Now I select the “Sum of Product X” table and use search and replace to replace “Sum of ” with a empty space. I also renamed the product column header:

Now the last thing that I need to do is get rid of the blanks and replace them with the same values as the previous row. Here I can use another Excel trick:

Start by selecting all the columns we want to fill the blanks of, go to the home tab and select “find and select”, and click “Go To special”. Here we can select
blanks and click ok. This will result in all the blanks being selected.  Type =, press the up arrow and press Ctrl+Enter (that’s hold down Ctrl and press the Enter key). All the
empty cells will be filled with the value of one row above. Exactly what we  wanted.

Now we can choose two options, load the table into PowerPivot or just use the regular Excel pivottable. I prefer getting my data into PowerPivot so let’s do that. Select a cell inside the table and go to the PowerPivot ribbon and click Create linked table. This will automaticly create a Excel table (make sure to select my table has headers) and load the data into PowerPivot:

Now we can create a new pivottable based on this table:

As final step we need to determine the Top 1 product per country, again we are going to use an Excel function. Select a product in the pivottable and click on the filter button on row labels. Now we select value filters, click Top 10 and change the number of items from 10 to 1 and press Ok.

This will give us the result we want:

As you can a lot can be done with Excel, you just have to know your way around. While this might not be a surprise for most of the Excel pro’s out there, we BI Pro’s are eager to run things through SQL or SSIS but it can just as easily be done by Excel. Maybe now I will be Excel gansta enough to join Rob Collie’s elite Excel group

I had an interesting question on my ask a questions page, one that I have (or something similar) heard a few times before. So I decided to do make a blog post on how to solve it

Our company tracks changes in the resource planning using a data warehouse. An example would be:

Assignment 1: Ian 8 hours planned for 3-Jun; valid from 1-jan-2011 until 1-feb-2011.
Assignment 1: Ian 6 hours planned for 3-Jun; valid from 2-feb-2011 until 15-feb-2011.
Assignment 1: Ian 7 hours planned for 3-jun; valid from 16-feb-2011 until infinity.

I what to enable our end-user to select any date level (Date, Week, Month, year) and get the current planning for that planned date (3-jun).

First of all I created a table in Excel to represent the data:

Next I create a date table that I can use to select dates at different levels using a slicer:

I did not create a relationship between the two tables because I want to use the values of the date table to search for values in the planning table. So far i have prepared the tables, next is creating the report.

First I created a workbook and a new pivottable with the slicers from the date table and put the employee name on rows:

now for the interesting part, on to the DAX formula. We want to use the values from the slicers to determine the date we want to search the planning table on. We can use values(columnname) to get the current value of the slicer and create a date from that on the fly. For example values(‘Date’[year]) will get the current value of years from the slicer. IF you join all 3 together you can create a new Date: Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day]))

Slicers can have multiple values selected at the same time so we need to make sure that only one is selected at the same time, otherwhise we cannot create this date. We can do this with the following formula’s:

=if( countrows(values(‘Date’[year])) = 1 &&countrows(values(‘Date’[Month])) = 1 &&countrows(values(‘Date’[Day])) = 1,

Now on to creating the DAX formula to finding the right values in the planning table:

=if( countrows(values('Date'[year])) = 1
&&
countrows(values('Date'[Month])) = 1
&&
countrows(values('Date'[Day])) = 1,
CALCULATE(sum(Rates[Hours]),
	FILTER(Rates,
	Rates[Startdate] <= Date(values('Date'[year]),values('Date'[Month]),values('Date'[Day]))
	&&
	(Rates[Enddate] >= Date(values('Date'[year]),values('Date'[Month]),values('Date'[Day]))
	|| ISBLANK(Rates[Enddate]))
	)
	)
,Blank()
)

Let’s look at this step by step:

First we want to calculate the sum of Hours.

CALCULATE (sum(Rates[Hours]),

for the rows in the selected date range:

FILTER(Rates,
Rates[Startdate] <= Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day]))
&&
(Rates[Enddate] >= Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day])) || ISBLANK(Rates[Enddate])) )

Filter the Rates table to return only the rows where
the Rates[Startdate] <= the selected date
AND
Rates[Enddate] >=the selected date OR the Rates[Enddate] is empty.

When this formula is used we get the value we wanted based on the slicer we selected:

Make sure you turn the following “Visually indicate items with no data”  for each slicer (right mouse click, slicer settings):

Another blog post with a similar question and answer you can find here: http://www.powerpivotblog.nl/get-values-within-a-start-and-end-date-using-powerpivot-dax

Got an interesting question today. The question was about creating sparklines for all the country’s that shows a running total of sales for the last 12 months (Rolling 12 months) based on the year that I select from a slicer. I use the trick I blogged about before to work with sparklines and PowerPivot, I would recommend reading it for some background.

First I want to start by creating the sheet that I want to use as a report. On this sheet I start by creating the slicer that I want to use, In this case Calendaryear:

Next I create a second sheet where I want to collect my data for the sparklines.

Because we want to show the running 12 months per year I would like to use only the months of one year that is the baseline of the running month. So we hook up the slicer from our report to the pivottable in the other sheet. Select the slicer, go to options and click on pivottable connections, here I can select the pivottable from the other sheet to be filtered as well:

Next we want to create a measure that creates our running total:

=if(COUNTROWS(values(DimDate[CalendarYear])) = 1,
			CALCULATE(SUM(FactSales[SalesAmount]),
				DATESBETWEEN(DimDate[Datekey],
					DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)
					, LASTDATE(DimDate[Datekey])))
			,blank())

What this DAX measure does is first of all check if only one year is selected using:

COUNTROWS(values(DimDate[CalendarYear])) = 1,

If more than one year is selected we return blank(). If only one year is selected we calculate the running total by doing a sum of FactSales[SalesAmount] by creating a date range using DatesBetween.

The start date of the range is determined by getting the first day of the current month (we know this because of the current row context) and then go back 11 months before that:

DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)

to the last date of the current month:

LASTDATE(DimDate[Datekey])

Using this in combination of calculate the sum will now calculate over the entire date range.

Now we have the data set up we can create the sparklines. First we need to copy the rows and paste them to the report and create the sparklines on the report page, check the previous blog post on how to create the sparklines.

I have uploaded the workbook for download here.

Check out this great video from the Office team on what Excel and PowerPivot can do for you and your organisation:

You can find the original video here.

In Excel 2010 MS introduced Sparklines, as described on the Excel blog:

For Excel 2010 we’ve implemented sparklines, “intense, simple, word-sized graphics”, as their inventor Edward Tufte describes them in his book Beautiful Evidence.  Sparklines help bring meaning and context to numbers being reported and, unlike a chart, are meant to be embedded into what they are describing.

Too bad the sparklines aren’t really tightly integrated with PowerPivot  as are other charts. In this post i’ll describe an easy way to implement sparklines on PowerPivot data, with use of slicers.

Of course we start by loading data into PowerPivot, next  we create the following workbook in PowerPivot:

To gain more insight in sales per country per month I would like to use the sparklines per country. Lets start by adding data in a new sheet

Make sure you connect this Pivottable to the slicers from my other sheet by using the connect slicers option as posted in this blog post.

Ok next we are going to add the country’s to the original workbook. Select the country’s and copy them. Paste hem into the original sheet:

Now for the sparklines, go to insert and click Line. we now have to select the source for the sparklines, in our case this is the pivottable in our newly created workbook, and select all data in the months:

next we have to select where we want to put the sparklines:

This will result in the sparklines being showed:

We want them to be a little wider so we select the cell with the sparkline and the adjacent cell and click Merge and center to let our sparkline span two cells, next we select another sparkline design, I also like to use the markers.

This results in the following workbook:

We can even use the slicers, as we can see here when we select 2008 we only have 8 months, this will show in the sparklines as well:

I love sparlines, and they aren’t that hard to implement, for more information on sparklines see:

http://blogs.msdn.com/excel/archive/2009/07/22/formatting-sparklines.aspx and http://www.msofficegurus.com/post/Excel-2010-Sparklines.aspx

Having played with Data mining in combination with PowerPivot in a previous blog post, i wanted to do data mining at the company i’m currently consulting at, they use Excel 2007 in combination with SSAS. In excel 2007 we do not have the power of the flattened pivot table.  So i had to think of a way to convert the pivot table to a regular excel table. And i found a way, since i have converted all my excel version to 2010 i had to find an older image with Excel 2007 to make this post. I have used the dutch Excel version in the screenshots, i hope you can follow it.

Let’s say i want to data mine on the Adventure works cube. First step is creating a regular pivottable containing all the values you want:

Next we want to put all rows into a regular column, right mouse click on the pivot table, and select Pivot table options, Go to the display tab and select classical pivot table layout. Press ok.

This will result in all rows being put in a column:

Next we remove all totals since we don’t need this in this data mining example, go to design, subtotals, do not show subtotals.

Ok we have made our data ready to be converted to a regular table, we can do this in 2 ways. Copy all cells and paste them in a new sheet or convert to formula’s and use that.

I copied and pasted the cell to a new worksheet, used the keep only values as paste option. This results in the following table:

the problem here is the blank values in the excel sheet, to fix this i found a solution on the web at Mr Excel.

Start by selecting all the columns we want to fill the blanks of, go to the home tab and select “find and select”, and click “Go To special”. Here we can select blanks and click ok.

This will result in all the blanks being selected.  Type =, press the up arrow and press Ctrl+Enter (that’s hold down Ctrl and press the Enter key). All the empty cells will be filled with the value of one row above. Exactly what we wanted.

Now convert it into a table (Ctrl – L) and you are a ready to unleash the data mining beast:

The SQL Server Data Mining team is working to extend the power and ease of use of SQL Server Data Mining to the Cloud. You can use this add-in to run datamining against a service in the cloud, you don’t need a SSAS instance installed ! Check it out on this page: http://www.sqlserverdatamining.com/cloud/

Here is what the add in looks like in Excel 2007:

So you can just start using datamining with PowerPivot as we have seen in my previous screencast but then without having SSAS installed on your network.

The guys from the data mining team even build an online data mining tool, so you don’t even have Excel installed, here you can load data from CSV files or from  SQL Data Services and data mine that.

Excel 2010 will be a developers heaven, the MS excel team has written a blog post on how you can get to certain data in a Excel sheet published on Excel Services 2010.

The opportunities for using REST are virtually endless. To spur your imagination, here’s a sampling of some ways to use the REST API:

• Embed a chart or range in a web page, blog post, etc.
• Embed a chart as a “linked” image into a Word document, PowerPoint presentation, or Outlook mail, and have always up-to-date information.
• Build a “mash-up” that uses Excel Services for calculation, charting, and/or as a data source.
• Build a Windows 7 gadget that displays information from an Excel workbook
• …and many more ideas I am sure our customers will think up.

Phew the SharePoint 2010 product will really be “social” and really enable self service. Read the entire post at their blog.