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: 
Thank you very much for the tip. It saves me a lot of time in my data processing work.
Hi Nhi or Kasper,
Do you have the same screen shots in an English version since you used the tip from Karper?
Thanks,
I am sorry I don’t.