Power Pivot in Excel 2010

Am I the only who is experiencing lots of problems with Power Pivot (PP)?

I have now spent over a week trying to find a solution and but no luck yet. I even asked the MS team support and was told to ask a question on one MS’s forums. No one has replied to my question yet.

This add-in PP is riddled with errors. MS has expected users of excel to be technical. If you have a problem with PP, some of the suggestions are: Uninstall PP, reinstall it, follow these instructions and so on.

Initially, I thought I was the only person who has a problem with PP, but having searching the net, I was taken back.

Most of the forums discussion concentrates on with Share point and Cube, but not lots on the basics of excel 2010. I do not use share point, so the discussion forum is not use to me.

I have the power pivot add-in in my excel window, and I do not see the issue would be installation, or configuration either, so the forum on installation is again not much help.

Everything seems to work fine when I set-up the PP for an existing excel file. I can see the field pan with slicers, but when I save the file and reopen it, the slicer pan disappears and the field pan appears to be that of Normal Pivot Table. After a while, I get an error saying the connection with source data is lost.” Initiation of the data source failed”.

In addition to the above error, I have found this error” External table is not in the expected format”. This errors appears when I tried to import an existing excel file. According to MS site, you can import any excel file from 97-2007 of any excel format. My excel sheet is a normal excel with xlxs- 2007 file. Why is not in the expected format?

Can someone kindly suggestion any solution to my predicament? I am keen to hear from pivot experts of David, Simon, Andrew and others. 

Comments
chanpangchi's picture

Can you rebuild your source

chanpangchi | | Permalink

Can you rebuild your source table from scratch?  e.g. copy data from your source table to MS Access table and then save the data from MS Access table back to a new Excel table.

 

Personally, I suggest to store all data in SQL server (or SQL Express) which is more roburst.

Power Pivot in Excel 2010

Cantona1 | | Permalink

Thanks for your comment, but I need to get the bottom of the problem as most of my works are based on excel.

If MS said you can import any data, why is my excel data which is created in excel 2007 is not in the expected format. Why I also losing a connection to my source data, despite my source data are the same as my existing open spreadsheet. I am not connecting to any external data. I have an open excel file

shurst's picture

How are you connecting to the data?

shurst | | Permalink

How are you connecting to the Excel data? If you are basing PowerPivot on Excel data within a single Excel workbook, are you opening the PowerPivot window from that workbook and using the 'Create Linked Table' option or are you obtaining the data in a different way? (Just to let you know, my responses might be a little erratic - in all senses - for the next day or two as I'm travelling through Scotland on trains...)

Hi Simon,

Cantona1 | | Permalink

Hi Simon,

Thanks for your help!

I have done the sample exercises from MS TechNet.

According to MS, there are two options of adding a PT

Add a pivot Table to your Analysis.

Important

“Always create Pivot Tables from the Power Pivot Window, or the Power Pivot tab in the excel window. There is also Pivot Table button on the insert tab in the Excel Window, but standard Excel Pivot Tables cannot access your Power Pivot data”

So, I know and see your point.

My PT is crated through a linked table, and the Power Pivot from the Power Pivot window. I can see a table is created on the PP window which has a link sign.

The problem starts when I re-open my excel spreadsheet, and try to add data to excel and refresh the Pivot Table. I get constant error message on excel saying that” The source data lost connection.

I have also tried by importing route- the same excel data, but I get another error.

“External table is not in the expected format”

According to MS, you can import any excel data, let alone an excel data which is created in 2007.

There is no explanation, or help from MS on the possible causes and suggested solutions.

May be my excel data is corrupted. However, I can set-up and run from the inserted Pivot table in Excel Window which is not part of the Power Pivot book and see no problem

shurst's picture

Linked tables

shurst | | Permalink

Thanks for the additional information. A few more questions. Is your Excel table very big - approximately how many columns and rows? Are you just linking to the one table in the PowerPivot window? I have had problems with PowerPivot when the computer hasn't had enough resources or memory. PowerPivot then just behaves erratically - for example refusing to believe a value field contains values. Closing Excel down and re-opening usually sorts the problem out. It might also be worth closing any other open applications to make sure as much resource as possible is free.

Finally, I did see in one of the forums that someone had a similar problem that they had traced to setting a filter in a PivotTable and then changing the data so that the value filtered on no longer existed. It might be worth clearing all filters before refreshing the PivotTable.

chanpangchi's picture

I found that long time ago if

chanpangchi | | Permalink

I found that long time ago if the Excel worksheet was big, then it could be corrupted frequently.  This is why I decided to store data on SQL Server and use Excel as analytical tool.  There are many ways to import an Excel worksheet, e.g. ADO, DAO.  Your Excel worksheet may be corrupted to a level that it can be loaded in one way but not the other.

I would suggest to try the same worksheets on another machine; if it does not help, then rebuild the Excel worksheet from scratch and see if it would help.

How much data do you have?  MS provides FREE SQL Express that can store up to 10 GB data.

My file is only 2.05MB. It

Cantona1 | | Permalink

My file is only 2.05MB. It has about 6 sheets with each not more than 70 rows, so I do not think it is the size of my file. Since I do not know what the cause is, I just put it as a corrupted file and move on. However, I am also encountering some new problems with Power Pivot.

Every time I open excel, Power Pivot is disabled, so I need to do the following:

Go to Excel Options on the file, Find Add-in, then Power Pivot, choose Excel Com. I have to do this every time I open an excel file. Why? I do not know.

The Group/Ungroup Button is also disabled, but I could not find any solution for this. My initial reaction was that My date data are not recognized by excel as date, but If I use the normal Pivot Table insert function, I can see the group/ungroup button.  

 

 

shurst's picture

Grouping

shurst | | Permalink

Hi

I have the same COM add-in problem and haven't yet found the answer either!

Regarding grouping, I believe this is because PowerPivot uses OLAP cubes which are not susceptible to ad-hoc grouping in the same way that standard PivotTable data is. The solution is to add columns to the data tables in the PowerPivot window to create the grouping there and then use these new fields to group the data in the PowerPivot report.

Hi Simon,

Cantona1 | | Permalink

Hi Simon,

Thank you!

You may be right with regards to Grouping. It is not only date group which is disabled, but all grouping in Power Pivot. In a normal PT, you can group different itmes by CTRL, but this is not available now.

Do you know what formula/s to use to group dates? Is it by E-month-end, quartely, or yearly? I suspect, I am going to use DAX expression?

 

shurst's picture

DAX expressions for grouping

shurst | | Permalink

Hi - yes, it will be a DAX expression, but one very similar to a normal Excel function. There is an added complication with months, in that, without using a custom sort, PowerPivot will sort months alphabetically. To avoid this, you could format the month include the number and name - something like:

=FORMAT(Invoices[OrderDate],"mm-mmm")

You might find Bill Jelen (MrExcel)'s book: 'PowerPivot for the data analyst' useful - it covers the DAX expressions in some detail including many Date/Time examples. I'm sure there are other PowerPivot books also available, as they say on the BBC.

I also had another thought on your original problem - it might be worth checking that your security settings are not disabling access to external data, at least when you have linked to your data as an external file.

 

Hi Simon,

Cantona1 | | Permalink

Hi Simon,

Thanks again!

I found the solution why the PP add-in has been disabled when I check my settings on the "Trusted Centre"

I think you may be right, but diffcult to know which box,/page to tick/Untick. I

just use what ever MS default set-up was. There are more 8 settings on the Trust Centre settings

 

 

shurst's picture

Trust Centre settings

shurst | | Permalink

I guess it would be the External Content section - I've set mine to prompt so I see a message in the message bar if something is blocked. What setting was causing your Add-in issue? I can't see anything obvious in my settings that would cause the problem - I'm not blocking all Add-ins as far as I can see.

 Simon: Thanks again!

Cantona1 | | Permalink

 Simon: Thanks again!

I have tried by changing all the settings, and the result is the same- when I open an excel, I still get the source connection failor. I will give up on power pivot until such time in the future. I spent over three weeks to fix the connection failor, but to no avail. I have tried different types of excel files suspecting that some of the files may be corrupted. Some time the PP works on some files, but when I try to refresh the data, I get the same error message- Source data connection.

I will use the noraml PT.

anthonymellor's picture

a salutary tale, thank you for the warning.

anthonymellor | | Permalink

I will approach this subject with less gung ho enthusiasm than I was about to.

Curious about SQL as I have that loaded for IRIS, but never touch it.

Anthony

anthonymellor's picture

"store all data in SQL server

anthonymellor | | Permalink

"store all data in SQL server (or SQL Express) "

how?

Add comment
Log in or register to post comments